如何将更新与选择查询相关联

Question

我想根据同一表中不同列中的值更新 SalesDifference20132015 表中 SalesDifference20132015 列中的每一行。我知道 SELECT 查询有效，但是当我将其插入更新查询时，我收到返回超过 1 个值的子查询错误。如何将我的选择查询与我的更新相关联？

UPDATE warehouse.StockItems
SET SalesDifference20132015 =
(SELECT (SELECT COUNT(*)
FROM Sales.Orders o
join sales.OrderLines ol ON o.OrderID = ol.OrderID
WHERE ol.StockItemID = si.StockItemID AND YEAR(o.OrderDate) = 2013
Group by ol.StockItemID)
-
(SELECT COUNT(*)
FROM Sales.Orders o
join sales.OrderLines ol ON o.OrderID = ol.OrderID
WHERE ol.StockItemID = si.StockItemID AND YEAR(o.OrderDate) = 2015
Group by ol.StockItemID)
FROM Warehouse.StockItems si
)

Answer 1

这样的事情应该可以工作：

UPDATE si
SET SalesDifference20132015 = DIFF.Diff
FROM
warehouse.StockItems si
JOIN
(
SELECT DISTINCT
StockItemID
,
(
SELECT COUNT(*)
FROM
Sales.Orders o
JOIN sales.OrderLines ol ON o.OrderID = ol.OrderID
WHERE ol.StockItemID = si.StockItemID AND YEAR(o.OrderDate) = 2013
GROUP BY ol.StockItemID
)
-
(
SELECT COUNT(*)
FROM
Sales.Orders o
JOIN sales.OrderLines ol ON o.OrderID = ol.OrderID
WHERE ol.StockItemID = si.StockItemID AND YEAR(o.OrderDate) = 2015
GROUP BY ol.StockItemID
) Diff
FROM Warehouse.StockItems si
) DIFF
ON si.StockItemID = DIFF.StockItemID