Re: SQL Help - update query with two tables

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

---------------------------------------------------
PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
To subscribe, unsubscribe, or to change your mail settings:
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss