mysql - MySQL中多种情况的语法

Question

我有一个 MySQL 更新语句，它试图重置两列:label 和 PersTrCode。 (这是在 Coldfusion 程序中。)我正在使用 CASE 语句执行此操作，但我似乎无法获得正确的语法——我不断收到错误消息。代码:

<cfquery name = 'nonull' datasource = "Moxart">
update FinAggDb
set Label = CASE 
 WHEN PersActIncOutg = 'I' && PersTrCode IS NULL THEN 'Total Income'
 WHEN PersActIncOutg = 'O' && PersTrCode IS NULL THEN 'Total Expense'
 WHEN PersActIncOutg IS NULL && PersTrCode IS NULL THEN ' '
 ELSE PersTrCode
END 
SET PersTrCode = CASE 
 WHEN PersTrCode IS NULL THEN 'Total'
 ELSE PersTrCode
END 
</cfquery>

错误是通常的信息性陈述:

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 PersTrCode = CASE WHEN PersTrCode IS NULL THEN 'Total' ELSE PersTrCode ' at line 8

是否不允许使用多个 CASE 语句？或者谁能​​告诉我如何解决这个问题？

Answer 1

更新语句只有一个 set 子句，您要更新的各个列用逗号分隔。另外，请注意，使用 and 而不是 && 更常见，尽管两者在 MySQL 中都有效:

update FinAggDb
set Label = CASE 
 WHEN PersActIncOutg = 'I' AND PersTrCode IS NULL THEN 'Total Income'
 WHEN PersActIncOutg = 'O' AND PersTrCode IS NULL THEN 'Total Expense'
 WHEN PersActIncOutg IS NULL AND PersTrCode IS NULL THEN ' '
 ELSE PersTrCode
END
,  -- comma here, not a second "set" clause
PersTrCode = CASE 
 WHEN PersTrCode IS NULL THEN 'Total'
 ELSE PersTrCode
END