使用CASE表达式更新SQL多行多列时语法报错，求解决方案

错误原因 #

你生成的SQL语句重复使用了SET关键字，这违反了UPDATE语法规范。UPDATE语句中SET只能出现一次，多个列的更新需用逗号分隔。

正确的SQL语句 #

UPDATE ORDERS_BACKUP 
SET MemberID = CASE OrderID 
                   WHEN '80087' THEN '5429' 
                   WHEN '80091' THEN '5429' 
               END,
    CustomerID = CASE OrderID 
                     WHEN '80087' THEN '2050' 
                     WHEN '80091' THEN '2050' 
                 END
WHERE OrderID IN ('80087', '80091') 

修正后的VBA代码 #

把第二个SET替换为逗号，同时去掉多余的分号：

sSQLOrderUpdate = "UPDATE ORDERS_BACKUP " &_
"SET MemberID = CASE OrderID " &_
"WHEN '" & iOrderID_1 & "' THEN '" & iMemberID & "' " &_
"WHEN '" & iOrderID_2 & "' THEN '" & iMemberID & "' " &_
"END, " &_
"CustomerID = CASE OrderID " &_
"WHEN '" & iOrderID_1 & "' THEN '" & iCustomerID & "' " &_
"WHEN '" & iOrderID_2 & "' THEN '" & iCustomerID & "' " &_
"END " &_
"WHERE OrderID IN ('" & iOrderID_1 & "','" & iOrderID_2 & "') "

额外提示 #

当前直接拼接字符串的写法存在SQL注入风险，建议使用参数化查询（比如通过ADODB.Command对象传递参数），既能提升安全性，也能避免因变量格式问题引发的语法错误。

内容的提问来源于stack exchange，提问作者Bjorgen Eatinger