Help with Update statement

mac4rfree · #1 (**permalink**) 04-06-10, 12:28

Hi Guys,

This is my first thread here. I am trying to update a column with the sum of the column(Col1) but it should be grouped by on another column (Col2)

Code:

update Table set Col1 = (select sum(Col1)
from Table
where Col3=1
group by Col1,Col2)
where Col1=1

This is the sample data am trying with

Code:

COl1 Col2 Col3
0.4   2     1
0.8    2     1
5    3      1

The output should be

Code:

1.2          2         1
1.2          2          1
5              3          1

Thanks For your help in advance.
Regards,
Magesh

ARWinner · #2 (**permalink**) 04-06-10, 12:50

Is this what you want?

Code:

update Table as t set Col1 = (select sum(Col1)
from Table as x
where x.Col3=1 and x.col2 = t.col2
)

Andy

mac4rfree · #3 (**permalink**) 04-06-10, 13:30

Yes Andy.. that worked like charm.. thanks again..

Internet Services

Web Development

Digital Marketing

Consumer Tech