mysql更新部分多行,在单个MySQL查询中更新多行

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?

解决方案

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')