加入更新的 MySQL 语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2114534/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL syntax for Join Update
提问by larjudge
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:
目前，我正在尝试创建一个查询，如果预订被取消，该查询将增加火车的容量。我知道我必须执行 Join，但我不确定如何在 Update 语句中执行此操作。例如，我知道如何使用给定的 ReservationID 获取火车的容量，如下所示：
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?
如果可能，我还想知道如何增加任意数量的座位。顺便说一句，我计划在 Java 事务中执行增量后删除保留。删除会影响交易吗？
Thanks for the help!
谢谢您的帮助！
回答by Bill Karwin
MySQL supports a multi-table UPDATEsyntax, which would look approximately like this:
MySQL 支持多表UPDATE语法，大致如下所示：
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 Traintable and delete from the Reservationstable in the same transaction. As long as you do the update first and then do the delete second, it should work.
您可以在同一事务中更新Train表和从Reservations表中删除。只要您先进行更新，然后再进行删除，它就应该可以工作。
回答by user3232196
Here is another example of an UPDATE statement that contains joins to determine the value that is being updated. In this case, I want to update the transactions.payee_id with the related account payment id, if the payee_id is zero (wasn't assigned).
这是 UPDATE 语句的另一个示例，其中包含用于确定正在更新的值的连接。在这种情况下，如果 payee_id 为零（未分配），我想用相关的帐户付款 ID 更新 transactions.payee_id。
UPDATE transactions t
JOIN account a ON a.id = t.account_id
JOIN account ap ON ap.id = a.pmt_act_id
SET t.payee_id = a.pmt_act_id
WHERE t.payee_id = 0
