How can I compose this query?

Question

I have a problem coming up with a query in MySQL. I have the following query:

SELECT
  tm.ticket_message_id, 
  tm.ticket_id, 
  tm.message_from, 
  tm.message_to, 
  t.member_id, 
  t.member_to 
FROM 
  `ticket_messages` AS tm 
  INNER JOIN `tickets` AS t 
  ON ( t.ticket_id = tm.ticket_id )

The primary key of table ticket_messages is ticket_message_id. For tickets, the primary key is ticket_id.

Right now, due to a mistake in a program's logic, the value in ticket_messages.message_from is always 0. This is wrong.

Now I must fix this situation and set tm.message_to to tm.message_to or t.member_id, depending on the following condition:

Suppose ID_1 and ID_2 are two IDs. If tm.message_from and t.member_to are both equal to ID_1, and t.member_id is equal to ID_2, then the value for tm.message_from needs to be assigned the value in t.member_id.

If tm.message_from and t.member_id are equal to ID_2, and t.member_to is equal to ID_1, then the value for tm.message_from needs to be assigned the value in t.member_to.

How I could fix my table with a MySQL query?

Answer 1

Use the awesome "CASE" statement.

UPDATE `ticket_messages` AS tm, `tickets` AS t
SET tm.message_from = CASE
  WHEN tm.message_from == t.member_id THEN t.member_id
  ELSE t.member_to
END
WHERE ( t.ticket_id = tm.ticket_id )