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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] 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 - [email protected] >>> To subscribe, unsubscribe, or to change your mail settings: >>> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss >> --------------------------------------------------- >> PLUG-discuss mailing list - [email protected] >> To subscribe, unsubscribe, or to change your mail settings: >> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss >> >> --------------------------------------------------- >> PLUG-discuss mailing list - [email protected] >> To subscribe, unsubscribe, or to change your mail settings: >> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss >> > > > ---------------------------------------------------------------------- > -- > > --------------------------------------------------- > PLUG-discuss mailing list - [email protected] > To subscribe, unsubscribe, or to change your mail settings: > http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss --------------------------------------------------- PLUG-discuss mailing list - [email protected] To subscribe, unsubscribe, or to change your mail settings: http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
