SQL Help - update query with two tables

[Alex Dean]

Bryan O'Neal wrote:
> I have not done SQL by hand since sometime in 2005/2006 and I can not 
> remember how to do a dynamic update using more then one table.
>  
> Let us say I have a table A and a table B and I want to update a record 
> in table A for every record in table b base on information in table B.  
> For example, let us say I want A.CURRENT_BALANCE to be set to 
> A.CURRENT_BALANCE + B.DEPOSIT where A.ACCOUNT_ID = B.ACCOUNT_ID.
>  
> Thoughts?

Hi Bryan.  When will you run this query to update A.CURRENT_BALANCE? 
You'll need to be sure that CURRENT_BALANCE isn't out of sync with the 
data in B.DEPOSIT.

There are other approaches that don't require you to denormalize your 
data.  If you run into performance problems, then it might be time to 
look at setting up a derived CURRENT_BALANCE, but I wouldn't start with 
that approach.

3 ideas :
1. Calculate CURRENT_BALANCE with an aggregate query like "SELECT 
ACCOUNT_ID, SUM( DEPOSIT ) AS CURRENT_BALANCE FROM B GROUP BY ACCOUNT_ID".

2. Create a view based on that query which you can use in other selects.

3. If you really want a separate column for CURRENT_BALANCE, set up a 
trigger on B to keep it updated.  Since a trigger will fire on every 
insert or update of B, your SQL can be much simpler.  (It only has to 
care about 1 row, not updating the entire table at once.)  The trigger 
would run something like "UPDATE A SET CURRENT_BALANCE = CURRENT_BALANCE 
+ NEW.DEPOSIT WHERE A.ACCOUNT_ID = NEW.ACCOUNT_ID"  That syntax is based 
on MySQL's flavor of triggers, but the idea should be adaptable to DB2. 
  NEW is the row in B after the insert or update you've just performed.

alex

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 252 bytes
Desc: OpenPGP digital signature
Url : http://lists.PLUG.phoenix.az.us/pipermail/plug-discuss/attachments/20080524/1b73e0f6/attachment.pgp