How to Update Train Capacity Based on Reservation Cancellation Using MySQL JOIN?

MySQL Syntax for Joint Updates

Problem:

You have two tables, Train and Reservations, and you want to update the capacity of a train when a reservation is canceled. You know how to select the capacity based on a ReservationID but need assistance with performing an update using a JOIN.

Solution:

Use the following multi-table UPDATE syntax:

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

This query increments the t.Capacity column by the corresponding r.NoSeats for the specified ReservationID.

Arbitrary Seat Increment:

To increment the capacity by an arbitrary number of seats, modify the SET clause:

SET t.Capacity = t.Capacity + <your_number_of_seats>

Transaction

Yes, you can delete the reservation after performing the increment within a single Java transaction. As long as the increment is done first, it will not be affected by the deletion.

The above is the detailed content of How to Update Train Capacity Based on Reservation Cancellation Using MySQL JOIN?. For more information, please follow other related articles on the PHP Chinese website!