SQL技术问询：如何基于Acquisitions表求和结果更新Stock表

看起来你在更新Stock表计算物料采购总和时踩了个常见的SQL坑——你的原查询因为固定了IdMat=1的条件，还错误地在子查询里关联了Stock表，导致所有行都被设置成同一个总和。别担心，咱们来拆解问题并给出正确的解决方案：

原查询的问题点 #

• 子查询中不必要地JOIN了STOCK表，并且硬编码了WHERE(s.IdMat=1)，这会让所有更新行都返回IdMat=1的采购总量，自然所有记录的Amount值都相同
• EXISTS子查询没有关联到当前要更新的STOCK行，导致只要存在任意匹配的采购记录，所有Stock行都会被更新

正确的SQL实现方案 #

方案1：关联子查询（通用兼容写法） #

这种写法适用于绝大多数关系型数据库（比如SQL Server、Oracle、MySQL等），通过关联当前更新的Stock行来计算对应物料的总和：

UPDATE STOCK s
SET Amount = (
    SELECT SUM(a.Amount)
    FROM Acquisitions a
    WHERE a.IdMat = s.IdMat
)
WHERE EXISTS (
    SELECT 1
    FROM Acquisitions a
    WHERE a.IdMat = s.IdMat
);

说明：

• 子查询通过s.IdMat关联到当前正在更新的Stock行，每个行都会独立计算对应IdMat的采购总量
• WHERE EXISTS确保只更新有对应采购记录的Stock行，避免没有采购记录的行被设置为NULL

方案2：UPDATE JOIN（高效写法，以MySQL为例） #

如果使用的是支持UPDATE JOIN语法的数据库（比如MySQL），可以先对采购数据分组求和，再关联更新，性能通常更优：

UPDATE STOCK s
JOIN (
    SELECT IdMat, SUM(Amount) AS TotalAmount
    FROM Acquisitions
    GROUP BY IdMat
) a ON s.IdMat = a.IdMat
SET s.Amount = a.TotalAmount;

说明：

• 先通过子查询对Acquisitions按IdMat分组计算总量，再将结果与STOCK表关联，直接更新对应行的Amount字段
• 这种方式减少了重复的子查询计算，数据量大时效率更高

额外补充：如果需要插入新物料记录 #

如果你的Stock表中可能没有某些已采购物料的记录，需要同时完成「更新现有记录+插入新记录」的操作，可以使用以下方式：

• MySQL：INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO STOCK (IdMat, Amount)
SELECT IdMat, SUM(Amount)
FROM Acquisitions
GROUP BY IdMat
ON DUPLICATE KEY UPDATE Amount = VALUES(Amount);

• SQL Server/Oracle：MERGE语句

MERGE INTO STOCK s
USING (
    SELECT IdMat, SUM(Amount) AS TotalAmount
    FROM Acquisitions
    GROUP BY IdMat
) a ON s.IdMat = a.IdMat
WHEN MATCHED THEN
    UPDATE SET s.Amount = a.TotalAmount
WHEN NOT MATCHED THEN
    INSERT (IdMat, Amount) VALUES (a.IdMat, a.TotalAmount);

内容的提问来源于stack exchange，提问作者Chep Soundz