具有内部联接的 SQL Server 更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3468006/
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
SQL Server Update with Inner Join
提问by Geoff
I have 3 tables (simplified):
我有 3 个表（简化）：
tblOrder(OrderId INT)
tblVariety(VarietyId INT,Stock INT)
tblOrderItem(OrderId,VarietyId,Quantity INT)
If I place an order, I drop the stock level using this:
如果我下订单，我会使用以下方法降低库存水平：
UPDATE tblVariety
SET tblVariety.Stock = tblVariety.Stock - tblOrderItem.Quantity
FROM tblVariety
INNER JOIN tblOrderItem ON tblVariety.VarietyId = tblOrderItem.VarietyId
INNER JOIN tblOrder ON tblOrderItem.OrderId = tblOrder.OrderId
WHERE tblOrder.OrderId = 1
All fine, until there are two rows in tblOrderItem with the same VarietyId for the same OrderId. In this case, only one of the rows is used for the stock update. It seems to be doing a GROUP BY VarietyId in there somehow.
一切正常，直到 tblOrderItem 中有两行具有相同的 VarietyId 和相同的 OrderId。在这种情况下，只有一行用于股票更新。它似乎以某种方式在那里做一个 GROUP BY VarietyId 。
Can anyone shed some light? Many thanks.
任何人都可以透露一些信息吗？非常感谢。
回答by gbn
My guess is that because you have shown us simplified schema, some info is missing that would determine why have the repeated VarietyID values for a given OrderID.
我的猜测是，因为您向我们展示了简化的架构，所以缺少一些信息来确定为什么给定 OrderID 具有重复的 VarietyID 值。
When you have multiple rows, SQL Server will arbritrarily pick one of them for the update.
当您有多行时，SQL Server 将任意选择其中之一进行更新。
If this is the case, you need to group first
如果是这种情况，您需要先分组
UPDATE V
SET
Stock = Stock - foo.SumQuantity
FROM
tblVariety V
JOIN
(SELECT SUM(Quantity) AS SumQuantity, VarietyID
FROM tblOrderItem
JOIN tblOrder ON tblOrderItem.OrderId = tblOrder.OrderId
WHERE tblOrder.OrderId = 1
GROUP BY VarietyID
) foo ON V.VarietyId = foo.VarietyId
If not, then the OrderItems table PK is wrong because if allows duplicate OrderID/VarietyID combinations (The PK should be OrderID/VarietyID, or these should be constrained unique)
如果不是，那么 OrderItems 表 PK 是错误的，因为如果允许重复的 OrderID/VarietyID 组合（PK 应该是 OrderID/VarietyID，或者这些应该被限制为唯一）
回答by Adriaan Stander
From the documentation UPDATE
从文档 更新
The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated (in other words, if the UPDATE statement is not deterministic).For example, given the UPDATE statement in the following script, both rows in table s meet the qualifications of the FROM clause in the UPDATE statement, but it is undefined which row from s is used to update the row in table t.
如果 UPDATE 语句包含的 FROM 子句未指定为每个更新的列出现只有一个值（换句话说，如果 UPDATE 语句不是确定性的），则 UPDATE 语句的结果是未定义的。例如，给定以下脚本中的 UPDATE 语句，表 s 中的两行都满足 UPDATE 语句中 FROM 子句的条件，但未定义使用 s 中的哪一行更新表 t 中的行。
CREATE TABLE s (ColA INT, ColB DECIMAL(10,3))
GO
CREATE TABLE t (ColA INT PRIMARY KEY, ColB DECIMAL(10,3))
GO
INSERT INTO s VALUES(1, 10.0)
INSERT INTO s VALUES(1, 20.0)
INSERT INTO t VALUES(1, 0.0)
GO
UPDATE t
SET t.ColB = t.ColB + s.ColB
FROM t INNER JOIN s ON (t.ColA = s.ColA)
GO
回答by Tobiasopdenbrouw
You are doing an update. It will update once.
你正在进行更新。它会更新一次。
Edit: to solve, you can add in a subquery that will group your orderitems by orderid and varietyid, with a sum on the amount.
编辑：要解决，您可以添加一个子查询，该子查询将按 orderid 和variableid 对您的订单项进行分组，并加上金额的总和。
