用于连接更新的 MySQL 语法

我有两张桌子是这样的

火车

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

预订

+---------------+-------------+------+-----+---------+----------------+
| 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    |                |
+---------------+-------------+------+-----+---------+----------------+

目前，我正在尝试创建一个查询，如果预订被取消，该查询将增加 Train 上的容量。我知道我必须执行一个 Join，但是我不知道如何在 Update 语句中执行它。例如，我知道如何使用给定的 Reserve ID 获取 Train 的容量，如下所示:

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

但是我想构造这样的查询-

Increment Train.Capacity by ReservationTable.NoSeats given a ReservationID

如果可能的话，我还想知道如何增加任意数量的座位。顺便说一句，我打算在 Java 事务中执行增量之后删除预订。删除会影响交易吗？

Thanks for the help!

111899

小开

最佳答案

MySQL supports a 多表 UPDATE语法, 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.