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:
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