Search code examples
sqlmysql

MySQL syntax for Join Update

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!

Solution

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

    Note that Microsoft SQL Server also supports syntax for joins in an UPDATE statement, but MySQL and Microsoft have each implemented this syntax differently. Both of them chose to extend standard SQL (which does not normally support any join syntax for UPDATE), and they did not implement the syntax in a compatible manner.