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.