使用SUM和Group BY进行T-SQL更新

使用SUM和Group BY进行T-SQL更新

如果此查询返回所需的信息：

SELECt *, LINE_TOTAL = SUM(l.LINE_TOTAL) OVER     (PARTITION BY l.IC_PO_HEADER, l.RELEASE_NUMBER)  FROM dbo.PO_HEADER AS h  INNER JOIN dbo.PO_LINE AS l  ON h.IC_PO_HEADER = l.IC_PO_HEADER   AND h.RELEASE_NUMBER = l.RELEASE_NUMBER;

然后，这可能是您想要的UPDATe查询：

;WITH x AS(  SELECT h.TOTAL, lt = SUM(l.LINE_TOTAL) OVER     (PARTITION BY l.IC_PO_HEADER, l.RELEASE_NUMBER)  FROM dbo.PO_HEADER AS h  INNER JOIN dbo.PO_LINE AS l  ON h.IC_PO_HEADER = l.IC_PO_HEADER   AND h.RELEASE_NUMBER = l.RELEASE_NUMBER)UPDATE x SET TOTAL = lt;

我必须同意戈登的观点，您的分组似乎很奇怪。我不确定我是否正确（这就是为什么我强烈建议您先运行SELECT的原因）。