问题描述:

I want the actual field updated to change depending on a condition. Existing examples I have come across only allow the value of the update to be changed, not the field. This is what I have tried:

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

However this gives me a syntax error.

How do I set the actual field updated to be conditional?

网友答案:

This should do the trick:

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;
网友答案:

You can always do something like this:

UPDATE conversations
SET 
    conv_viewed_author = (
        CASE conv_author_id 
            WHEN $uid THEN $d 
            ELSE conv_viewed_author 
        END
    ),
    conv_viewed_recipient = (
        CASE conv_author_id 
            WHEN $uid THEN conv_viewed_recipient 
            ELSE $d 
        END
    )
WHERE conv_id = $id
相关阅读:
Top