Mysql 脚本使用 if 语句和 case 进行更新

Question

为什么我不能使用或者为什么会遇到错误。我正在尝试使用如下脚本。我是 mysql 的新手，我尝试创建这样的脚本，但出现如下错误

#1064 - 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 '1 THEN dogs.dogcount = dogcount - dogdetails.total, dogdetails.statu' at line 4 

这是我的脚本

UPDATE dogs 
LEFT JOIN dogdetails ON dogs.username = dogdetails.username 
SET dogs.dogcount = 
CASE WHEN dogdetails.dogcount IS NOT 1 THEN dogs.dogcount = dogcount -    dogdetails.total, dogdetails.status = 'Checked'
WHEN dogdetails.dogcount = 1 THEN dogs.pto = pto - dogdetails.total, dogdetails.status = 'Checked'
WHERE dogdetails.id=4

有什么我遗漏或忽视的吗？

Answer 1

请记住，您必须在 case 语句后使用关键字“end”并始终返回一个值。比较数字时 IS NOT 关键字也不正确。这是您应该可以使用的查询版本:

UPDATE dogs 
LEFT JOIN dogdetails ON dogs.username = dogdetails.username 
SET dogs.dogcount = CASE WHEN dogdetails.dogcount != 1 THEN dogs.dogcount = dogcount-dogdetails.total else dogs.dogcount end, 
dogs.pto= case WHEN dogdetails.dogcount = 1 THEN  pto - dogdetails.total else pto end,     
dogdetails.status = 'Checked'
WHERE dogdetails.id=4