SQL: Update Multiple Rows With Different values

Question

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

What database are you using? SQL Server? MySQL? Or something else? — Matthew Haugen, Jul 31 '14 at 02:50
Square brackets around names suggests Sql Server, since MySQL uses backticks and Oracle uses doublequotes. — Barmar, Jul 31 '14 at 02:51
@Barmar Fair enough. It'd still be nice for the OP to tag it, though. — Matthew Haugen, Jul 31 '14 at 02:52
update PO set totalCost = sum(POLines.cost) FROM PO inner join POLines ON PO.id = POLines.id this should be the something that you looking for — ah_hau, Jul 31 '14 at 02:54
i'm getting the following error: An aggregate may not appear in the set list of an UPDATE statement. — Frank_Vr, Jul 31 '14 at 02:57
possible duplicate of [Update a table using JOIN in SQL Server?](http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server) — Barmar, Jul 31 '14 at 03:00
yeh true, although Barmar provided an answer with out a join which is what i'm using — Frank_Vr, Jul 31 '14 at 03:11

Barmar · Accepted Answer · 2014-07-31T03:03:57.740

1

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)

edited Jul 31 '14 at 03:03

answered Jul 31 '14 at 02:56

Barmar

741,623
53
500
612

I tried this in SQLFiddle and the first one doesn't work. You can't join a query like that. I've never shared a SQL Fiddle before, so I hope this works, but [this is it](http://sqlfiddle.com/#!6/ef5e6/2). Just in case I missed something silly. – Matthew Haugen Jul 31 '14 at 02:57
The second one is spot on, though. – Matthew Haugen Jul 31 '14 at 02:58
I just linked to a question as a possible duplicate, it shows how to use JOIN in an UPDATE in SQL-Server. I'll see if I can rewrite my answer according to that. – Barmar Jul 31 '14 at 03:01
thanks heaps this is what i was after, the 2nd one works – Frank_Vr Jul 31 '14 at 03:01
@Barmar sounds good. Although that doesn't address the aggregate matter, which is what makes this particularly difficult. You *might* be able to do it with a CTE, but I'm far from skilled with those. I think standard practice in SQL, at least from what I'd do, is just to have the subquery as in your second example. It's definitely just as readable and efficient as any other solution, in any event. – Matthew Haugen Jul 31 '14 at 03:03
@Frank_Vr I've updated the first query. It doesn't get a syntax error in the fiddle. But since the tables are both empty, it doesn't do anything, so I can't tell for sure that it works. But I can't see why it wouldn't. – Barmar Jul 31 '14 at 03:06

SQL: Update Multiple Rows With Different values

1 Answers1