加入更新的 MySQL 语法

2021-11-20 00:00:00 sql 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    |                |
+---------------+-------------+------+-----+---------+----------------+

目前，我正在尝试创建一个查询，如果预订被取消，该查询将增加火车的容量.我知道我必须执行 Join，但我不确定如何在 Update 语句中执行此操作.例如，我知道如何使用给定的 ReservationID 获取火车的容量，如下所示:

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

如果可能，我还想知道如何增加任意数量的座位.顺便说一句，我计划在 Java 事务中执行增量后删除保留.删除会影响交易吗?

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?

感谢您的帮助！

推荐答案

MySQL 支持 多表UPDATE 语法，大致如下所示:

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 = ?;

您可以在同一事务中更新 Train 表并从 Reservations 表中删除.只要先更新再删除就可以了.

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.

相关文章