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
– Matthew Haugen, Commented Jul 31, 2014 at 2:50
Square brackets around names suggests Sql Server, since MySQL uses backticks and Oracle uses doublequotes. — Barmar
– Barmar, Commented Jul 31, 2014 at 2:51
@Barmar Fair enough. It'd still be nice for the OP to tag it, though. — Matthew Haugen
– Matthew Haugen, Commented Jul 31, 2014 at 2: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
– ah_hau, Commented Jul 31, 2014 at 2:54
i'm getting the following error: An aggregate may not appear in the set list of an UPDATE statement. — Frank_Vr
– Frank_Vr, Commented Jul 31, 2014 at 2:57

Barmar · Accepted Answer · 2014-07-31 03:03:57Z

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, 2014 at 3:03

answered Jul 31, 2014 at 2:56

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

6 Comments

Matthew Haugen Over a year ago

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. Just in case I missed something silly.

Matthew Haugen Over a year ago

The second one is spot on, though.

Barmar Over a year ago

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.

Frank_Vr Over a year ago

thanks heaps this is what i was after, the 2nd one works

Matthew Haugen Over a year ago

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

|

Collectives™ on Stack Overflow

SQL: Update Multiple Rows With Different values

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related