Update a group of Records as SUM? 1

[RovirozM]

Hi Guys,

I have this records:

Key1 A 100
Key1 B 200
Key1 C 300
Key 2 D 400

I would like to Get the Key1 and Sum all the values(600) and put that on another Field.

It is possible to do it with an Update sentence?

Update MyTable
Set MyField = SUM(KEY1)
Group By ...

Or I need to do a Loop or other thing?

Thanks a lot

MR

 

[markros]

Yes, you can do this with update statement.

update myTable set myFild = T.Total from myTable 
inner join (select Key, sum(Value) as Total from myTable group by Key) T on myTable.Key = T.Key

The techique is called derived table technique, but you could have used common table expressions as well.

PluralSight Learning Library

 

[RovirozM]

Hi Markros or all,

Thanks for the statements it looks very good... It is a subquery that Sums the values I need and then Update the statement (I understand that).

I'm trying to use it and I don't know why I'm getting this error:


This is my Query:

Update MPS_Commodity_Wrk
Set MPS_Commodity_Wrk.OnHandQty = T.Total from SwsHol_INSSS S
Inner Join ( Select Swshol_INSSS.Part_Number, sum(SwsHol_INSSS.Quantity_On_Hand)as Total
From SwsHol_INSSS
Where (SwsHol_INSSS.Part_Number = '15854415')
And (SwsHol_INSSS.Stores_account = 'SERVFIN' or SwsHol_INSSS.Stores_account = 'SERVLC')
Group By Swshol_INSSS.Part_Number)T on MPS_Commodity_Wrk.PartNumber = t.Part_Number


I'm getting this error :

Msg 107, Level 16, State 3, Line 1
The column prefix 'MPS_Commodity_Wrk' does not match with a table name or alias name used in the query.


Or where can I find a Good Guide about Subqueries With updates Statements in order to find this error?

Thanks a lot!

 

[markros]

The error is self-explanatory. See

Update MPS_Commodity_Wrk Set MPS_Commodity_Wrk.OnHandQty = T.Total from SwsHol_INSSS S

and you're using S table and not MPS_Commodity_Wrk

PluralSight Learning Library