SQL Help - update query with two tables

[Bryan O'Neal]

Hmm..  I really should use a cursor shouldn't I.

Thanks Joseph, I think I am going to read up on cursors in DB2 and may
have more questions for you soon.

-----Original Message-----
From: plug-discuss-bounces at lists.plug.phoenix.az.us
[mailto:plug-discuss-bounces at lists.plug.phoenix.az.us] On Behalf Of
Joseph Sinclair
Sent: Friday, May 23, 2008 7:16 PM
To: Main PLUG discussion list
Subject: Re: SQL Help - update query with two tables

UPDATE TABLEA
SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 =
TABLEA.a and TABLEB.e1 > 40) WHERE EXISTS (SELECT 1 from TABLEB WHERE
TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40);

Not pretty, but should work.
You also might find this to work MUCH better as a stored procedure using
a cursor...

Bryan O'Neal wrote:
> IBM DB2
>  
> 
> -----Original Message-----
> From: plug-discuss-bounces at lists.plug.phoenix.az.us
> [mailto:plug-discuss-bounces at lists.plug.phoenix.az.us] On Behalf Of 
> Joseph Sinclair
> Sent: Friday, May 23, 2008 7:09 PM
> To: Main PLUG discussion list
> Subject: Re: SQL Help - update query with two tables
> 
> What's the server (i.e. Oracle, MySQL, PostgreSQL, etc...)
> 
> 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?
>>
>>
>>
>> ---------------------------------------------------------------------
>> -
>> --
>>
>> ---------------------------------------------------
>> PLUG-discuss mailing list - PLUG-discuss at lists.plug.phoenix.az.us
>> To subscribe, unsubscribe, or to change your mail settings:
>> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss at lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
> 
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss at lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>