CODEWITHSUNDEEP
mysql
Anylyn Hax
Anylyn Hax

Reputation: 361

mysql: updating/adding to a column in the same table

I have a table

calls DATE(DATE) GENDER(VARCHAR(1)) NAME(VARCHAR(100))

I want to do this :

UPDATE calls WHERE GENDER="M" SET NAME = CONCAT("sir: ",NAME) ;
UPDATE calls WHERE GENDER="F" SET NAME = CONCAT("mrs: ",NAME) ;

but it is not working, How to do it ?

Upvotes: 0

Views: 34

Answers (2)

derpirscher
derpirscher

Reputation: 17436

You can even do this in a single statement for both cases

update calls set name = case 
    when gender = 'f' then concat('mrs: ', name)
    when gender = 'm' then concat('mr: ', name)
    else name
  end;

See this fiddle

https://www.db-fiddle.com/f/q2FtjGusyjdL2xPJuGtSJK/3

Upvotes: 2

trillion
trillion

Reputation: 1401

UPDATE calls SET NAME = CONCAT("sir: ",NAME) WHERE GENDER="M"  ;

use set name before the where clause

Upvotes: 1

Related Questions