CODEWITHSUNDEEP
mysqlsql
Preethi Raman
Preethi Raman

Reputation: 1

Query to update particular column field in SQL

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;

Upvotes: 0

Views: 75

Answers (3)

tonlls
tonlls

Reputation: 3

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

ALTER TABLE role CHANGE Admin Administrator [Admin column data type]

Upvotes: -2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 2

Joakim Danielson
Joakim Danielson

Reputation: 51892

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

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

Upvotes: 4

Related Questions