Mysql update query to update a column in many rows

Question

I have a table with two columns a and b. Something like :

     a  |  b
   +----+----+
     x  |  l
     y  |  m
     z  |  n

Using a single mysql update query, I want to update a column in many rows ...

update tableName set b = l1,m1,n1 where a= x,y,z respectively.

what should the update query for this be like?

Editing my question to make it more clear : I dont want to append a 1 in the column values. I want to update them to new values which are not like the old ones. So is there any way to do that using a single MYSQL query?

Basically, I want to combine these queries into one :

update tableName set b=newVal, where a=something;
update tableName set b=anotherNewVal, where a=something_else;
update tableName set b=yetAnotherNewVal, where a=something_else_again;

Thanks much !

`update tableName set b = concat(b, '1') where a in ( 'x','y','z')`? — Giorgos Betsos, Oct 09 '15 at 06:57

score 1 · Accepted Answer · answered Oct 10 '15 at 06:02

1

Thanks a lot for all your help. @Giorgos Betsos , @mynawaz and @Mathew. I found the query !

update tableName 
set b = case 
     when a ='x' then 'a is x' 
     when a ='y' then 'a is y' 
     else b 
end;

answered Oct 10 '15 at 06:02

Anjana

873
7
22

score 0 · Answer 2 · answered Oct 09 '15 at 07:04

0

Try this

UPDATE tableName SET b = CONCAT(b, '1') WHERE a = 'x' OR a = 'y' OR a = 'z'

OR

UPDATE tableName SET b = CONCAT(b, '1') WHERE a IN ('x', 'y', 'z')

answered Oct 09 '15 at 07:04

mynawaz

1,599
1
9
16

@Giorgos Betsos Do you mind explaining CONCAT(b, '1')? What will it do? Will it set the value in column b to 1? – Anjana Oct 09 '15 at 08:26
@Ridhima, `CONCAT(b, '1')` will concatenate the value of column `b` with `1` i.e. if the column `b` has value `m`, it will return `m1`, if the column `b` has value `n` it will return `n1` and so on. And `SET b = CONCAT(b, '1')` will concatenate the value of column `b` with `1` and set it to column `b` – mynawaz Oct 09 '15 at 08:58
oh okay.. actually that isnt what I am looking for. I probably should have made the question clearer. What I want is to not append 1. m and m1 are totally different. I want to update entirely different values in the columns using one query. Is there any way to do that? – Anjana Oct 09 '15 at 12:00
@Ridhima, you should use `CASE` or `IF` then. `CASE` is better suited to your requirement as done by Mathew in his answer – mynawaz Oct 09 '15 at 12:12

score 0 · Answer 3 · answered Oct 09 '15 at 07:16

0

 UPDATE `tableName`
 SET `tableName`.`b`= 
 CASE 
 WHEN `tableName`.`b`='l'  THEN 'l1'
 WHEN `tableName`.`b`='m'  THEN 'm1'
 WHEN `tableName`.`b`='n'  THEN 'n1'
 END
 WHERE `tableName`.`a`  IN ('x','y','z');

answered Oct 09 '15 at 07:16

Mathew

296
1
7

can you suggest me a better way.. so that I can use it if two rows have same value at column a? – Anjana Oct 09 '15 at 08:20
I tried this query : `update tableName set b = (case when a='x' then 'a is x' when a='y' then 'a is y' end );` It made this update, but also made all other values in column b to NULL. Can you please help me ? – Anjana Oct 10 '15 at 05:28
`update tableName set b = (case a when 'x' then 'a is x' when 'y' then 'a is y' end);` query also does the same. Sets all other values to null. – Anjana Oct 10 '15 at 05:44

Mysql update query to update a column in many rows

3 Answers3