Query to update particular column field in SQL

45
February 12, 2019, at 12:30 PM

I tried the following queries to change the column field of the table 'role' from 'Admin' to 'Administrator'. I am not informed about the values of id. my table 'role' contains two columns 'name' and 'id'. None of the queries worked. Where am I going wrong?

update table role set name=replace('Administrator','Admin');
update table role set name='Administrator' where name='Admin';
select replace('Admin','Administrator') from role;
Answer 1

You don't use a table keyword so the second example is almost correct

update role set name='Administrator' where name='Admin';
Answer 2

The second would be the standard way:

update role  -- "table" is not appropriate
    set name = 'Administrator'
    where name = 'Admin';

My guess is that you have no row that matches the condition -- presumably because of bad/hidden characters in name. Try this:

select *
from role
where name = 'Admin';

If this returns no rows, then try like:

where name like 'Admin%'
where name like '%Admin'
where name like '%Admin%'

Or maybe, using regexp:

where name regexp '[[:<:]]admin[[:>:]]'

(note that regexp is, by default, not case sensitive)

If one of these matches the row you want, then use it in the update.

Answer 3

you need to alter the table structure so the Mysql comand should be:

ALTER TABLE role CHANGE Admin Administrator [Admin column data type]
READ ALSO
How to query a date using strftime to format

How to query a date using strftime to format

I am trying to grab all the records for the monthThe string I have to query with is in this format 2019-01-12

39
Extract data from a database if a specific value in is followed by a another specific value

Extract data from a database if a specific value in is followed by a another specific value

I have a database in MySQL where one of the columns consists of different types of events and I want to extract specific data to pythonFor example:

56
SQL, Group by one column with one distinct column

SQL, Group by one column with one distinct column

I have a table with following structure

42
Get sum using DATE_FORMAT() and IN

Get sum using DATE_FORMAT() and IN

I've got a query that looks a little bit like this :

51