MySQL CASE...WHERE...THEN 语句

2022-01-17 00:00:00 sql-update mysql

我有一个使用 CASE 子句的 MySQL UPDATE 语句

I have a MySQL UPDATE statement that uses a CASE clause

UPDATE partsList SET quantity =  
CASE
  WHEN partFK = 1 THEN 4
  WHEN partFK = 2 THEN 8
END
WHERE buildFK = 1;

上述语句有效.然而，当我删除其中一个 WHEN 语句时，它会中断并且错误表明 CASE 子句没有返回任何内容.是不是 CASE 子句必须有多个 WHEN 才能起作用.

The above statement works. Yet when I remove one of the WHEN statements, it breaks and the error indicates the CASE clause isn't returning anything. Is it that the CASE clause must have more than one WHEN to function.

我事先不知道我需要多少更新，所以我正在尝试构建一个可以处理一个或多个更新的更新语句.

I don't know beforehand how many updates I'll need, so I'm trying to build a single update statement that can handle one or many updates.

感谢您提供的任何见解.

Thanks for any insights you can provide.

推荐答案

并不是说CASE一定不止一个，WHEN...THEN，就是它必须处理你给它的所有数据.

It isn't that the CASE must have more than one, WHEN...THEN, it's that it must handle all the data you give it.

如果您删除了其中一个条款，就会留下一个漏洞.例如

If you removed one of the clauses, you leave a hole. e.g.

UPDATE partsList SET quantity =  
CASE
  WHEN partFK = 1 THEN 4
END
WHERE buildFK = 1;

使用此更新语句，如果 parkFK 为 2，则更新失败，因为 CASE 无法处理输入.

With this update statement, if parkFK is 2, then the update fails because the CASE can't handle the input.

您可以通过在 where 子句中添加另一行来限制您的源数据(例如 AND partFK in (1,2))，或者您可以添加一个 ELSE 到 case 表达式.

You can either limit your source data by adding another line to your where-clause (e.g. AND partFK in (1,2)), or you could add an ELSE to the case expression.

UPDATE partsList SET quantity =  
CASE
  WHEN partFK = 1 THEN 4
  WHEN partFK = 2 THEN 8
  ELSE 12 
END
WHERE buildFK = 1;

但是，根据您展示的 SQL 语句，可能有更好的方法.据推测，partFK 是某个其他表的外键.你能从那里提取 quantity 的值吗?

However, based on the SQL statement you've shown, there is probably a better way. Presumably, partFK is a foreign-key to some other table. Can you pull the value for quantity from there?

相关文章