I want to update two different columns from two different tables in mysql. This is the brief version of what I want to do.
In more detail I want to update a column from table 1 only if a condition is true. Otherwise I want to update a other column from table 1.
The same I want to do for table 2 and this should be all done in a single UPDATE statement.
I tried something like this:
UPDATE `grouped_messages`
JOIN `messages` ON (`messages`.group_msg_id = 1) SET
(CASE WHEN(`grouped_messages`.`conversation_partner1`=10) THEN `grouped_messages`.`deletion_status_partner1`=1
WHEN(`grouped_messages`.`conversation_partner2`=10) THEN `grouped_messages`.`deletion_status_partner2`=1 END),
(CASE WHEN(`messages`.`from`=10) THEN `messages`.`deletion_status_from`=1
WHEN(`messages`.`to`=10) THEN `messages`.`deletion_status_to`=1 END);
but this is not working. Does anybody know a solution, which would work for my case?
You can't do an assignment within a CASE expression. Your SET clause is invalid. It needs to be SET column = expression.
To have the column conditionally updated; that is, to have the column not updated, have the expression return the current value of the column.
Let's use an alias of g for grouped_messages.
When the condition you are checking is true, you want the SET clause to be equivalent to:
SET g.deletion_status_partner1 = 1
When the condition is not true, you want to assign the current value of the column to itself, so the SET clause to equivalent to:
SET g.deletion_status_partner1 = g.deletion_status_partner1
To get that, you could do something like this:
SET g.deletion_status_partner1
= CASE WHEN g.conversation_partner1 = 10
THEN 1
ELSE g.deletion_status_partner1 -- existing column value
END
To modify an additional column, you'd need to add to the SET clause
, g.deletion_status_partner2
= CASE WHEN some_other_condition
THEN new_value
ELSE g.deletion_status_partner2 -- existing column value
END
(I haven't checked anything else in your statement; I only highlight the most obvious issue, and how to get an update statement to do "conditional" assignment.)