SQL Help - update query with two tables

[Joseph Sinclair]

You can't update a row with more than one value.  Your select where clause must be specific enough to ensure only one result (it looks like that may not be the case, based on the error you're getting).  If you have a many-many relationship between the two tables, the only way to get correct results is to write a procedure using a cursor.

As far as the Cursor approach, I think (based on a somewhat limited recollection of DB2 syntax) that you should be able to drop the FOR UPDATE clause on the cursor, since you're not updating A IN the cursor, but using it's output in a separate UPDATE statement.
I'd need to see the error you're getting from the SP to more fully assess why it's not working.

Bryan O'Neal wrote:
> I can not get this to work either, I get the following
> 
>> [Error] Script lines: 1-1 --------------------------
>  DB2 SQL error: SQLCODE: -811, SQLSTATE: 21000, SQLERRMC: null
>  Message: The result of a scalar fullselect, SELECT INTO statement, or
> VALUES INTO statement is more than one row. 
> 
> -----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: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 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
>>>
>>
>> ----------------------------------------------------------------------
>> --
>>
>> ---------------------------------------------------
>> 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
>