How to Perform a Multi-Table Update (JOIN UPDATE) in MySQL?

MySQL Syntax for Join Update

In MySQL, a multi-table update is possible with a specific syntax. This syntax allows the update of multiple tables, based on a join condition.

Implementing a Join Update

Consider the following scenario: you wish to increment a train's capacity when a reservation is canceled. To achieve this, you can use a join update query as follows:

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

where "?" represents the reservation ID associated with the canceled reservation.

Incrementing by an Arbitrary Number

The above query can be modified to increment capacity by an arbitrary number of seats:

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

Transaction Behavior

When incrementing the train capacity and deleting the reservation in a single transaction, it's crucial to follow a specific order. As long as you update the Train table before deleting from the Reservations table, the transaction will maintain its integrity.

The above is the detailed content of How to Perform a Multi-Table Update (JOIN UPDATE) in MySQL?. For more information, please follow other related articles on the PHP Chinese website!