Updating multiple rows in single column

Question

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?

Yogesh Sharma · Accepted Answer · 2020-02-18 09:07:56Z

2

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.

answered Feb 18, 2020 at 9:07

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parsia Shiraz · Accepted Answer · 2020-02-18 12:21:39Z

0

correlated is :

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

answered Feb 18, 2020 at 12:21

Parsia Shiraz

692 silver badges13 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2020-02-18 12:38:32Z

0

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

answered Feb 18, 2020 at 12:38

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Updating multiple rows in single column

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related