title: MySQL syntax for Join Update
body: <p>I have two tables that look like this </p>
  
  <p>Train</p>
  
  <pre><code>+----------+-------------+------+-----+---------+-------+
  | Field    | Type        | Null | Key | Default | Extra |
  +----------+-------------+------+-----+---------+-------+
  | TrainID  | varchar(11) | NO   | PRI | NULL    |       |
  | Capacity | int(11)     | NO   |     | 50      |       |
  +----------+-------------+------+-----+---------+-------+
  </code></pre>
  
  <p>Reservations</p>
  
  <pre><code>+---------------+-------------+------+-----+---------+----------------+
  | 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    |                |
  +---------------+-------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>select Capacity 
    from Train 
    Join Reservations on Train.TrainID = Reservations.Train 
   where ReservationID = "15";
  </code></pre>
  
  <p>But I'd like to construct the query that does this -</p>
  
  <pre><code>Increment Train.Capacity by ReservationTable.NoSeats given a ReservationID
  </code></pre>
  
  <p>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?</p>
  
  <p>Thanks for the help!</p>