On 4 Feb 2005 12:28:25 -0800, gooday wrote:
Table test2 has multiple amounts for each account, I would like to sum
the amounts for the same account and use the result to update the
variable 'tot_amount' in table test1. But SQL does not allow me to use
sum function in update. Is there any other way to do this? Thanks.
Hi gooday,
The best option is to not do this at all. Don't store values that you can
calculate, just calculate them when you retireve the data, as part of the
SELECT statement ((or put it in a view if you want it easy).
If you do store it, you'll soon find differences between the stored data
and the actual data and you'll find yourself plastering the database with
triggers to keep the calculated column current after each update.
Anyway, if you still feel that you have a valid reason for doing it like
this, here is how you could accomplish it:
UPDATE test1
SET tot_amount = (SELECT SUM(b.amount)
FROM test2 AS b
WHERE b.acc_no = test1.acc_no)
This will update all rows in test1; those without matching rows in test2
will have tot_amount set to NULL. If you prefer 0, use COALESCE:
UPDATE test1
SET tot_amount = COALESCE((SELECT SUM(b.amount)
FROM test2 AS b
WHERE b.acc_no = test1.acc_no), 0)
And if you prefer not to update the tot_amount value for rows that have no
match in the test2 table (as your non-working query would do - but I don't
think you intended that!), then use
UPDATE test1
SET tot_amount = (SELECT SUM(b.amount)
FROM test2 AS b
WHERE b.acc_no = test1.acc_no)
WHERE EXISTS (SELECT *
FROM test2 AS b
WHERE b.acc_no = test1.acc_no)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
