Ok, I am trying to use cursors but I am not having any successes
DECLARE C1 CURSOR WITH HOLD FOR
SELECT ACCOUNT_ID, DEPOSIT
FROM B
FOR UPDATE OF A
DO UNTIL SQLCODE = 100
FETCH C1
INTO :ACCOUNT-VAR, :DEPOSIT-VAR
UPDATE A
SET CURRENT_BALANCE = CURRENT_BALANCE + : DEPOSIT-VAR
WHERE ACCOUNT_ID = :ACCOUNT-VAR
END-DO
But no dice. Can I get a primer on how to do this?
-----Original Message-----
From:
plug-discuss-bounces@lists.plug.phoenix.az.us
[
mailto:plug-discuss-bounces@lists.plug.phoenix.az.us] On Behalf Of
Joseph Sinclair
Sent: Friday, May 23, 2008 7:21 PM
To: Main PLUG discussion list
Subject: Re: SQL Help - update query with two tables
Sorry, hit send before rewriting to match your query:
UPDATE A Set (CURRENT_BALANCE) = (SELECT A.CURRENT_BALANCE + B.DEPOSIT
From B WHERE A.ACCOUNT_ID = B.ACCOUNT_ID) WHERE EXISTS (SELECT 1 From B
WHERE A.ACCOUNT_ID = B.ACCOUNT_ID)
Joseph Sinclair wrote:
> 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@lists.plug.phoenix.az.us
>> [mailto:plug-discuss-bounces@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@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@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@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@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@lists.plug.phoenix.az.us
To subscribe, unsubscribe, or to change your mail settings:
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
(text/plain)