MySQL更新错误

Question

UPDATE `Starcraft2uvsu` 
SET IF(`Starcraft2uvsu`.username =  'wasd',  'report1',  'report2' ) =  'Win' 
FROM `Starcraft2uvsu` 
WHERE `Starcraft2uvsu`.username =  'wasd'

phpmyadmin 吐出的错误是...

#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 
'If(Starcraft2uvsu.username = wasd, 'report1', 'report2') = 'Win' FROM Starcraft2' at line 1 

甚至可以通过 if 语句设置列名吗？我想这就是凶手。

神奇的查询是答案和评论的组合..

UPDATE Starcraft2uvsu 
SET report1 = CASE WHEN Starcraft2uvsu.username = 'wasd' THEN 'win' ELSE report1 END,
    report2 = CASE WHEN Starcraft2uvsu.username <> 'wasd' THEN 'win' ELSE report2 END
WHERE Starcraft2uvsu.username = 'wasd' OR Starcraft2uvsu.username2 = 'wasd'

Answer 1

很难从语法中准确判断您要更新的内容。您想要使用类似于此的 CASE 语句:

UPDATE Starcraft2uvsu 
SET report1 = CASE WHEN Starcraft2uvsu.username = 'wasd' THEN 'win' ELSE report1 END,
    report2 = CASE WHEN IFNULL(Starcraft2uvsu.username,'') <> 'wasd' THEN 'win' ELSE report2 END
FROM Starcraft2uvsu 
WHERE Starcraft2uvsu.username = 'wasd'