MySQL UPDATE语句中CASE WHEN语法错误，求正确写法

你当前的UPDATE语句语法错误，核心原因是在SET子句中错误地将CASE用作流程控制语句——SQL里的CASE是表达式，必须返回具体值来赋值给字段，而非在分支里直接写赋值逻辑。

原错误查询语句 #

UPDATE swipes SET
CASE
    WHEN swp_from = :me AND swp_to = :user THEN first_swp = 'rewind'
    WHEN swp_to = :me AND swp_from = :user THEN second_swp = 'rewind'
END

错误信息 #

Warning: PDOStatement::execute(): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASE WHEN swp_from = '39' AND swp_to = '22' THEN f...' at line 2 in G:\xampp\htdocs\kelvie\processes\revoke.php on line 19

正确写法 #

以下是两种符合逻辑的可行方案：

方案1：针对字段分别使用CASE表达式

这种方式精准对应你的需求，仅在符合条件时修改目标字段，否则保留原值：

UPDATE swipes
SET 
    first_swp = CASE WHEN swp_from = :me AND swp_to = :user THEN 'rewind' ELSE first_swp END,
    second_swp = CASE WHEN swp_to = :me AND swp_from = :user THEN 'rewind' ELSE second_swp END
WHERE (swp_from = :me AND swp_to = :user) OR (swp_to = :me AND swp_from = :user);

• 新增的WHERE子句可以避免遍历更新全表，大幅提升执行效率，同时防止误改无关数据

方案2：用IF函数简化写法（MySQL/MariaDB专属）

如果觉得CASE表达式过于繁琐，可使用MySQL原生支持的IF函数替代：

UPDATE swipes
SET 
    first_swp = IF(swp_from = :me AND swp_to = :user, 'rewind', first_swp),
    second_swp = IF(swp_to = :me AND swp_from = :user, 'rewind', second_swp)
WHERE (swp_from = :me AND swp_to = :user) OR (swp_to = :me AND swp_from = :user);

内容的提问来源于stack exchange，提问作者Mr. Crypto