UPDATE语句中的MySQL查询CASE WHEN THEN

Question

我试图执行以下语句，但它导致了错误。这里没有太多可以解释的情况，因为查询本身是不言自明的。然而，我将在下面附上错误：

查询

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:xampphtdocskelvieprocessesrevoke.php on line 19

我知道关键字SET和CASE不能结合在一起，这导致了这个错误。但这就是查询的框架方式。以这个例子为例，有人能指导我如何正确地构建查询以匹配完全相同的期望吗？

Answer 1

在UPDATE语法中，必须具有SET column = <expression>。您可以设置多个列，但它们必须都是column = <expression>。不能将赋值包装在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;

ELSE表示，如果条件不成立，则设置first_swp = first_swp，即不更改。

您也可以在两次更新中完成这一操作，这将使代码更加清晰，如果索引支持它，则应该运行得更快，因为它不必检查每一行。

START TRANSACTION;
UPDATE swipes SET first_swp = 'rewind'
WHERE swp_from = :me AND swp_to = :user;
UPDATE swipes SET second_swp = 'rewind'
WHERE swp_to = :me AND swp_from = :user;
COMMIT;

事务是为了确保两个更改都已提交，否则两者都未提交。

Answer 2

您无法选择要动态更新的列。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