T-SQL Update with SUM and Group BY

Question

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?

Solution 1

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).

Solution 2

It is returning more than one column because the subquery select is returning multiple rows.

Perhaps you mean something more like this:

with toupdate as (
     SELECT SUM(LINE_TOTAL) AS "NEW_LINE_TOTAL" 
     FROM PO_LINE pl join
          PO_HEADER ph 
          on 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
    )
UPDATE PO_HEADER
    SET TOTAL = toupdate. New_Line_Total
    from toupdate
    where PO_HEADER.IC_PO_HEADER = toupdate.IC_PO_HEADER;

I'm still suspicious of this, though, because you are grouping by 5 fields in the subquery, but only joining on one of them. I would expect "toupdate" to be grouping only on ic_po_header in this situation.

Comments

[Shahab]

I am getting "Incorrect syntax near the keyword 'GROUP'" with that update statement. To test, I have created tables: create table PO_HEADER (TOTAL int, IC_PO_HEADER int, RELEASE_NUMBER int) create table PO_LINE (LINE_TOTAL int, IC_PO_HEADER int, RELEASE_NUMBER int)

[Adir D]

@Ian I can't debug your query if I can't see it, sorry.

[Shahab]

Thanks for taking notice. The error occurs when running your update statement (after running the "create table" statements above).

[Shahab]

That works fine thanks. The magical "with update" statement. (P.S. why is my "@Aaron" removed from the front of my comment?)

[Adir D]

@Ian because you are commenting on a post that I authored (so I get a notification without having to notify me explicitly).