问题引入
有这样一张表,我想这样修改,当creator为某个值时,将creator修改为另外一个值,当operator为某个值时修改为另外一个值
愚蠢且错误的解决方案
UPDATE tab
CASE
creator
WHEN '1' THEN
SET '2'
END CASE
CASE
operator
WHEN '5' THEN
SET '6'
END CASE
WHERE
id = '10'
正确的解决方案
UPDATE tab
SET creator =
CASE
creator
WHEN '1' THEN
'2' ELSE creator
END,
operator =
CASE
operator
WHEN '5' THEN
'6' ELSE operator
END
WHERE
id = '10'
特别注意要用到 ELSE creator 、ELSE operator ,否则当不满足条件时,就会被替换为空,使用这两个else就是代表如果不满足条件,就还是原本的值;