MySQL syntax for Join Update

Question

I have two tables that look like this

Train

+----------+-------------+------+-----+---------+-------+ | Field    | Type        | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | TrainID  | varchar(11) | NO   | PRI | NULL    |       | | Capacity | int(11)     | NO   |     | 50      |       | +----------+-------------+------+-----+---------+-------+ 

Reservations

+---------------+-------------+------+-----+---------+----------------+ | Field         | Type        | Null | Key | Default | Extra          | +---------------+-------------+------+-----+---------+----------------+ | ReservationID | int(11)     | NO   | PRI | NULL    | auto_increment | | FirstName     | varchar(30) | NO   |     | NULL    |                | | LastName      | varchar(30) | NO   |     | NULL    |                | | DDate         | date        | NO   |     | NULL    |                | | NoSeats       | int(2)      | NO   |     | NULL    |                | | Route         | varchar(11) | NO   |     | NULL    |                | | Train         | varchar(11) | NO   |     | NULL    |                | +---------------+-------------+------+-----+---------+----------------+ 

Currently, I'm trying to create a query that will increment the capacity on a Train if a reservation is cancelled. I know I have to perform a Join, but I'm not sure how to do it in an Update statement. For Example, I know how to get the capacity of a Train with given a certain ReservationID, like so:

select Capacity    from Train    Join Reservations on Train.TrainID = Reservations.Train   where ReservationID = "15"; 

But I'd like to construct the query that does this -

Increment Train.Capacity by ReservationTable.NoSeats given a ReservationID 

If possible, I'd like to know also how to Increment by an arbitrary number of seats. As an aside, I'm planning on deleting the reservation after I perform the increment in a Java transaction. Will the delete effect the transaction?

Thanks for the help!

Answer 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.

Answer 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