MySQL:使用If else更新查询

Question

我想要更新表，但我不知道要更新哪一列。下面是一个简单的例子

Update tablename
set 
if col1='A' then col1='IA',col2='XXX'
if col3='A' then col3='IA', col4='XXX'
where
(col1='A' and col2='UNKNOWN') or (col3='A' and col4='UNKNOWN')

在这里，col1或col3肯定包含'A'

请为我提供正确的查询

Answer 1

尝试此查询

UPDATE tablename
SET col1=(CASE WHEN col1 LIKE 'A' THEN col1='IA' ELSE col1 END),
col2=(CASE WHEN col1 LIKE 'A' THEN col2='XXX' ELSE col2 END),
col3=(CASE WHEN col3 LIKE 'A' THEN col3='IA' ELSE col3 END),
col3=(CASE WEHN col3 LIKE 'A' THEN col4='XXX' ELSE col4 END)
WHERE
(col1='A' AND col2='UNKNOWN') OR (col3='A' AND col4='UNKNOWN')

Answer 2

和老帖子，但对于那些偶然发现这一点的谷歌人来说。这行不通的。例如：

THEN col1='IA'

将返回一个布尔值，并将col1设置为1，即true，只返回带有THEN 'IA' ELSE col1 END的'IA'

为了清楚起见：

UPDATE tablename
SET col1=(CASE WHEN col1 LIKE 'A' THEN 'IA' ELSE col1 END),
col2=(CASE WHEN col1 LIKE 'A' THEN 'XXX' ELSE col2 END),
col3=(CASE WHEN col3 LIKE 'A' THEN 'IA' ELSE col3 END),
col3=(CASE WEHN col3 LIKE 'A' THEN 'XXX' ELSE col4 END)
WHERE
(col1='A' AND col2='UNKNOWN') OR (col3='A' AND col4='UNKNOWN')