Help needed in an Updation Query

Hai ,
Can any one of, out there help me in the following query.
I have two tables, one table has to get updated with sum of another tables
value. (Explanation below:)
Is it possible to use a single update statement, (i have done it with using
cursors)
Explanation:
Table A:
Col1 Col2 col3 col4
1 100 341 1.0
2 100 342 2.0
Table B:
Col1 Col2 col3 col4
1 100 341 0.1
2 100 341 0.2
3 100 341 0.3
4 100 342 0.4
5 100 342 0.5
6 100 342 0.6
Col2 of Table A PK, and Col2 of Table B id FK similarily
Col3 of Table A PK, and Col3 of Table B id FK
I need to updated Table A's Col4 column with the Sum of Col4(table B)
grouped by Col3 (table b) at one stretch.
If i am not clear in the above explanation , the query will be
Q1)
update A set col4 = col4 + (select sum(col4) from B group by col3 where
b.col2 = 100 and b.col3 = 341) where A.col2 = 100 and A.col3 = 341
Q2)
update A set col4 = col4 + (select sum(col4) from B group by col3 where
b.col2 = 100 and b.col3 = 342) where A.col2 = 100 and A.col3 = 342
when executed separately. I need these two queries in one single shot using
inner join(if possible) . I tried to do it, but unable to do it.
Thanks in advance,
V.Boomesshupdate A
set col4=col4+(select sum(B.col4) from B where B.col2=A.col2 and
B.col3=A.col3)
-oj
"Boomessh" <Boomessh@.discussions.microsoft.com> wrote in message
news:405E39EE-43BC-4373-B82A-B62B7C5502E3@.microsoft.com...
> Hai ,
> Can any one of, out there help me in the following query.
> I have two tables, one table has to get updated with sum of another tables
> value. (Explanation below:)
> Is it possible to use a single update statement, (i have done it with
> using
> cursors)
> Explanation:
> Table A:
> Col1 Col2 col3 col4
> 1 100 341 1.0
> 2 100 342 2.0
>
> Table B:
> Col1 Col2 col3 col4
> 1 100 341 0.1
> 2 100 341 0.2
> 3 100 341 0.3
> 4 100 342 0.4
> 5 100 342 0.5
> 6 100 342 0.6
>
> Col2 of Table A PK, and Col2 of Table B id FK similarily
> Col3 of Table A PK, and Col3 of Table B id FK
> I need to updated Table A's Col4 column with the Sum of Col4(table B)
> grouped by Col3 (table b) at one stretch.
> If i am not clear in the above explanation , the query will be
> Q1)
> update A set col4 = col4 + (select sum(col4) from B group by col3 where
> b.col2 = 100 and b.col3 = 341) where A.col2 = 100 and A.col3 = 341
> Q2)
> update A set col4 = col4 + (select sum(col4) from B group by col3 where
> b.col2 = 100 and b.col3 = 342) where A.col2 = 100 and A.col3 = 342
> when executed separately. I need these two queries in one single shot
> using
> inner join(if possible) . I tried to do it, but unable to do it.
>
> Thanks in advance,
> V.Boomessh
>
>|||Hai,
Thanks that worked.
V.Boomessh
"oj" wrote:

> update A
> set col4=col4+(select sum(B.col4) from B where B.col2=A.col2 and
> B.col3=A.col3)
>
> --
> -oj
>
> "Boomessh" <Boomessh@.discussions.microsoft.com> wrote in message
> news:405E39EE-43BC-4373-B82A-B62B7C5502E3@.microsoft.com...
>
>