CODEWITHSUNDEEP
sqlsql-server
Frank_Vr
Frank_Vr

Reputation: 659

SQL: Update Multiple Rows With Different values

I have one table that contains many rows, i was hoping there is a way to update a Total cost column in this table calculated from the sum of another Table.

is it possible to do something along the lines of this:

UPDATE [PO] set TotalCost=(Select sum(Cost) from [PO-Lines] where PO=Update.PO)

this is so i don't have to create a loop

Upvotes: 0

Views: 621

Answers (1)

Barmar
Barmar

Reputation: 780723

I don't know SQL-Server, so I'm extrapolating from MySQL and hoping I get the syntax right. You can do it either with a JOIN:

UPDATE t1
SET t1.TotalCost = t2.Total
FROM [PO] AS t1
JOIN (SELECT POId, SUM(Cost) Total
      FROM [POLines]
      GROUP BY POId) AS t2
ON t1.ID = t2.POId

or a correlated subquery:

UPDATE [PO]
SET TotalCost = (SELECT SUM(Cost) FROM [PO-Lines] WHERE [PO-Lines].POId = [PO].ID)

Upvotes: 1

Related Questions