Updating Records with Data From Other Records in Same Table 1

[DPilsbury]

I'm not sure if this is possible, but....

Given this table structure:

myTable
mainID subID Amount
A1 A1 10.0
A1 A2 10.0
A1 A3 10.0
B1 B1 0.0
B1 B2 25.0
B1 B3 25.0

Is there a way to write a single UPDATE statement that
would SUM the Amount field for all like mainID records and
place it into the record where mainID = subID.

Thus the result would look like this:

myTable
mainID subID Amount
A1 A1 30.0
A1 A2 10.0
A1 A3 10.0
B1 B1 50.0
B1 B2 25.0
B1 B3 25.0

I know this can be done with incorporating views or
within a cursor...I was just trying to figure out if
it can be done in an update statement.

Thanks.

 

[sunila7]

Try this

update tblname set Amount = (Select sum(Amount) From #t t1 where #t.mainid=t1.mainid group by mainid )
Where
mainid=subid


Sunil

 

[DPilsbury]

That did it!

Thanks Sunil.

I didn't know you could use group by in the set statement.

 

[SQLSister]

There is one problem with what you want to do that you need to link about. This update will only work once. After it will give wrong answers because the total includes the sub categories.
You start with:
myTable
mainID subID Amount
A1 A1 10.0
A1 A2 10.0
A1 A3 10.0
B1 B1 0.0
B1 B2 25.0
B1 B3 25.0

First results
myTable
mainID subID Amount
A1 A1 30.0
A1 A2 10.0
A1 A3 10.0
B1 B1 50.0
B1 B2 25.0
B1 B3 25.0

Results of running the query a second time
myTable
mainID subID Amount
A1 A1 50.0
A1 A2 10.0
A1 A3 10.0
B1 B1 100.0
B1 B2 25.0
B1 B3 25.0

You need to store the totals in a separate column or a separate table that is used for the main totals only.

 

[DPilsbury]

Thanks SQLSister, you are correct.

But that's ok as this update statement will only be run when the database is fully refreshed with data that is exported from another system.

 

[SQLSister]

Good, just wanted to make sure that you were aware.