MySQL syntax for Join Update
mysql, sql
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.
Problem
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!