MySQL syntax for Join Update

Solution 1:

MySQL supports a multi-table UPDATE syntax, which would look approximately like this:

UPDATE Reservations r JOIN Train t ON (r.Train = t.TrainID)
SET t.Capacity = t.Capacity + r.NoSeats
WHERE r.ReservationID = ?;

You can update the Train table and delete from the Reservations table in the same transaction. As long as you do the update first and then do the delete second, it should work.

Solution 2:

Here is another example of an UPDATE statement that contains joins to determine the value that is being updated. In this case, I want to update the transactions.payee_id with the related account payment id, if the payee_id is zero (wasn't assigned).

UPDATE transactions t
  JOIN account a ON a.id = t.account_id
  JOIN account ap ON ap.id = a.pmt_act_id
  SET  t.payee_id = a.pmt_act_id
 WHERE t.payee_id = 0

MySQL syntax for Join Update

Solution 1:

Solution 2:

Related

Recent Posts