mysql-使用多个where子句更新单行中的多个列

Question

我有一个mysql表如下:

name | serialno | key
---------------------
NULL | NULL     | 10
luke | 1234     | 20
NULL | NULL     | 30
NULL | NULL     | 40

我必须更新一行，其中 name 和 serialno 更新为有效 key 。

我正在运行的更新语句如下:

UPDATE test SET Name = 'pc', Serialno = '10', WHERE `Key` = '10' AND name is null

和

UPDATE test SET Name = CASE
WHEN NULL THEN 'pc'
END
SET Serialno = CASE
WHEN NULL THEN '10'
END
Where `key` = '20'

显示以下错误:

1.

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 'WHERE Key = '10' AND name is null' at line 1: 

2.

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 'SET Serial = CASE
WHEN NULL THEN '10'
END
Where `key`='20'' at line 4: 

请帮忙。

Answer 1

UPDATE test SET Name = CASE Name
WHEN NULL THEN 'pc'
END,
SET Serialno = CASE Serialno 
WHEN NULL THEN '10'
END
Where `key` = '20'

您必须指定您的案例条件