CODEWITHSUNDEEP
sqlsql-servert-sqlsql-update
Emiliano
Emiliano

Reputation: 109

Updating multiple rows in single column

I need to update newly added column(store the sum of all items of the invoice) with result from subquery which generates more than one rows. I added new column to Invoice table. 

ALTER TABLE Invoice
ADD ItemsSum int NULL

I tried following query but it gave error because of multiple results

UPDATE Invoice
SET ItemsSum = (SELECT SUM(Amount)
               FROM InvoiceItem it
               INNER JOIN Invoice i ON it.InvoiceID = i.ID
               GROUP BY i.ID)

How to achieve this correctly in SQL Server?

Upvotes: 0

Views: 353

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Yogesh's answer is correct, but if you like, you can also do this using an explicit aggregation:

UPDATE i
    SET ItemsSum = ii.sumAmount
FROM Invoice i LEFT JOIN
     (SELECT ii.InvoiceID, SUM(Amount) as sumAmount
      FROM InvoiceItem it
      GROUP BY ii.InvoiceID
     ) ii
     ON ii.InvoiceID = i.ID

Upvotes: 0

Parsia Shiraz
Parsia Shiraz

Reputation: 69

correlated is :

UPDATE Invoice
     SET ItemsSum = (SELECT SUM(invc.Amount) 
                     FROM InvoiceItem invc 
                     WHERE invc.InvoiceID = Invoice.ID
                     GROUP BY Invoice.ID;
                    );

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use correlated subquery : 

UPDATE Invoice
     SET ItemsSum = (SELECT SUM(it.Amount) 
                     FROM InvoiceItem it 
                     WHERE it.InvoiceID = Invoice.ID
                    );

ItemsSum will be invalid if underlying invoice amount will be change.

Upvotes: 2

Related Questions