使用 SUM 和 Group BY 的 T-SQL 更新 [英] T-SQL Update with SUM and Group BY

问题描述

我想使用此查询中的值进行更新，但它说它返回了多个值.

更新 PO_HEADERSET TOTAL = (SELECT SUM(LINE_TOTAL) AS "NEW_LINE_TOTAL"FROM PO_LINE pl, PO_HEADER ph其中 ph.IC_PO_HEADER = pl.IC_PO_HEADER和 ph.RELEASE_NUMBER = pl.RELEASE_NUMBER按 pl.IC_PO_HEADER、pl.FOREIGN_KEY、ph.RELEASE_NUMBER 分组，ph.REVISION_NUMBER,ph.PO_NUMBER)来自 PO_HEADER ph, PO_LINE pl其中 ph.IC_PO_HEADER = pl.IC_PO_HEADER;

<块引用>

消息 512，级别 16，状态 1，第 1 行
子查询返回了 1 个以上的值.当子查询跟在 =、!=、<、<=、>、>= 或当子查询用作表达式时，这是不允许的.

它如何返回不止一列?

解决方案

如果这个查询返回了你想要的信息:

SELECT *, LINE_TOTAL = SUM(l.LINE_TOTAL) OVER(PARTITION BY l.IC_PO_HEADER, l.RELEASE_NUMBER)从 dbo.PO_HEADER AS h内部连接 ​​dbo.PO_LINE AS l开 h.IC_PO_HEADER = l.IC_PO_HEADERAND h.RELEASE_NUMBER = l.RELEASE_NUMBER;

那么这可能就是你想要的 UPDATE 查询:

;with x AS(选择 h.TOTAL, lt = SUM(l.LINE_TOTAL) 超过(PARTITION BY l.IC_PO_HEADER, l.RELEASE_NUMBER)从 dbo.PO_HEADER AS h内部连接 ​​dbo.PO_LINE AS l开 h.IC_PO_HEADER = l.IC_PO_HEADERAND h.RELEASE_NUMBER = l.RELEASE_NUMBER)更新 x SET TOTAL = lt;

我不得不同意戈登，你的分组看起来很奇怪.我不确定我是否做对了(这就是为什么我强烈建议您先运行 SELECT).

I want to update with the value in this query, but it's saying it returns more than one value.

UPDATE PO_HEADER
  SET TOTAL = (SELECT SUM(LINE_TOTAL) AS "NEW_LINE_TOTAL" 
  FROM PO_LINE pl, PO_HEADER ph 
  where ph.IC_PO_HEADER = pl.IC_PO_HEADER 
  and ph.RELEASE_NUMBER = pl.RELEASE_NUMBER 
  group by pl.IC_PO_HEADER,pl.FOREIGN_KEY,ph.RELEASE_NUMBER,
  ph.REVISION_NUMBER,ph.PO_NUMBER)
from PO_HEADER ph, PO_LINE pl
where ph.IC_PO_HEADER = pl.IC_PO_HEADER;

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How is it returning more than one column?

解决方案

If this query returns the information you want:

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;

Then this is probably the UPDATE query you want:

;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;

I have to agree with Gordon, your grouping seems very strange. I'm not sure if I got it right (which is why I strongly recommend you run the SELECT first).

这篇关于使用 SUM 和 Group BY 的 T-SQL 更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！