SQL help nedded......

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Manny - 21 Sep 2006 18:46 GMT

Hello all,

Is it possible to do a sum in a sub-query which is grouped and then use
that to update a value in the main query?
Not sure if I have explained myself very well so.....

The table below has the column 'amount' which I want to update
(where dept = 'A101' and level = 0 and code = code from table
sub_start):

select * from tot_amount
+------+------+--------+-------+

+------+------+--------+-------+

|A101 |AA1234| 0| 0|
|A101 |AA1234| 1| 0|
|B303 |AA1234| 0| 0|
|A101 |BB1234| 0| 0|
|A101 |BB1234| 1| 0|

+------+------+--------+-------+

The table below has the values which I need to group and use to update
the amount in the above table:

select * from sub_start
+------+------+---------+

|Dept |Code |qty |

+------+------+---------+

|A101 |AA1234| 20|
|B303 |AA1234| 10|
|A101 |AA1234| 10|
|A101 |AA1234| 10|
|A101 |BB1234| 10|
|B303 |BB1234| 2|
|A101 |BB1234| 16|

+------+------+---------+

e.g. the amount value for:
row 1 should be updated to 40 (20 + 10 + 10)
row 2 should be 0
row 3 should be 0
row 4 should be 26 (10 + 16)
row 5 should be 0

Can this be done in one query?

Thanks in advance,

Manny

Reply to this Message

Karl & Betty Schendel - 21 Sep 2006 19:14 GMT

>Hello all,
>
>Is it possible to do a sum in a sub-query which is grouped and then use
>that to update a value in the main query?

No, alas. Scalar subqueries (which is what you describe) are not
yet allowed on the right-hand side of an update SET assignment.

Maybe someday...until then, either use a view, or declare a global
temporary table containing the intermediate sums.

Karl

Reply to this Message

Manny - 21 Sep 2006 21:06 GMT

Ok....thanks for clarifying that for me.

Manny.

> >Hello all,
> >
[quoted text clipped - 8 lines]
>
> Karl

Reply to this Message