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!

Angeliaangelic

I know this is a 9 year old post, but for something like train capacity, you don't want to be updating a column for this unless you have a really good reason. As you pointed out, it's a single join. This is something that will update A LOT - per reservation, so this should be on the fly select query with join, rather than update. Excessive updates lock tables. – For

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.

Rogerrogerio

Bah, I put the SET before the JOIN by mistake. NB folks – Everywhere

If you're in MySQL safe mode (default), then you can use WHERE alias.primary_key_id > 0 to effectively circumvent safe mode and update all rows. – Sankaran

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

Gnaw

If you may be wondering what the significance of the 2nd JOIN to account (aliased as ap), that was just because I first wrote the query as a SELECT (which is always good practice) prior to converting to UPDATE statement. – Gnaw