Update multiple rows in a single MySQL query

Question

I am trying to run this:

UPDATE test 
SET col2=1 WHERE col1='test1', 
SET col2=3 WHERE col1='test2';

The error I am getting:

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '

My table:

CREATE TABLE `test` (
    `col1` varchar(30) NOT NULL,
    `col2` int(5) DEFAULT NULL,
    PRIMARY KEY (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

It’s something about , at the end of the first row. When I changed it to ;, it didn’t recognize col2. How can I do this in one query?

Answer 1

This is most clear way

UPDATE test
SET col2 = CASE col1
WHEN 'test1' THEN 1
WHEN 'test2' THEN 3
WHEN 'test3' THEN 5
END,
colx = CASE col1
WHEN 'test1' THEN 'xx'
WHEN 'test2' THEN 'yy'
WHEN 'test3' THEN 'zz'
END
WHERE col1 IN ('test1','test2','test3')

Answer 2

Consider using INSERT-ODKU (ON DUPLICATE KEY UPDATE), because that supports to update multiple rows.

Make sure that the values of all PK columns are in the VALUES().

Where feasible, generate the SQL with data from a slave.