How can I update multiple rows in mysql with different criteria in WHERE?

Question

for eg

Set col1 to A WHERE id=X 
set col1 to B WHERE ID=Y

ID is unique but not the primary key. I'd like to do this in one update query

Answer 1

You can use a CASE expression to set the value according to the ID:

UPDATE yourtable
SET col1 = CASE ID WHEN X THEN A
                   WHEN Y THEN B
                   ELSE col1
           END

Demo on SQLFiddle

Note the use of an ELSE clause so that the value of col1 doesn't get changed when ID is not equal to X or Y. That can also be achieved with a WHERE clause:

UPDATE yourtable
SET col1 = CASE ID WHEN X THEN A
                   WHEN Y THEN B
           END
WHERE ID IN (X, Y)

Demo on SQLFiddle

Answer 2

You should use multiple statements for that like so:

update table1 set col1 = 'A' where id = 1;
update table1 set col1 = 'B' where id = 2;