MySQL根据条件更新不同的字段

Question

我希望实际的 field 更新为根据条件改变。我遇到的现有示例只允许更改更新的值，而不是字段。这是我尝试过的:

UPDATE conversations 
CASE WHEN conv_author_id = $uid THEN SET conv_viewed_author = $d
ELSE SET conv_viewed_recipient = $d END 
WHERE conv_id = $id

但是这给了我一个语法错误。

如何将实际更新的字段设置为有条件的？

Answer 1

这应该可以解决问题:

UPDATE conversations
SET
  conv_viewed_author = IF(conv_author_id = $uid, $d, conv_viewed_author),
  conv_viewed_recipient = IF(conv_author_id = $uid, conv_viewed_recipient, $d)
WHERE
  conv_id = $id;