Re: HELP RE. SQL UPDATE table1 with info from table2

One side note. A correlated sub query (like below) can be expensive if you are using any form of database logging. For each row in table1, a temp table is created and destroyed. If there are a large number of rows in table1 and/or the available log space is low, you could be the victim of a long transaction which would be aborted. If this is a possibility, I prefer to do row-level processing in an SPL by using a FOREACH loop and a single UPDATE within the loop. If you want a specific example, mail me and I will send or post one. I am running short on time today. Rob McAllister rmcall@on-ramp.ior.com johnl@informix.com (Jonathan Leffler) wrote: >Craig, Viggo, >Craig's answer addresses the same problem as what I understood from your >question, and gives more or less the answer I was going to give, but the >WHERE clause on the UPDATE statement must contain a criterion such as: >UPDATE Table1 > SET (Field1, Field3) = > ((SELECT Field1, Field3 > FROM Table2 > WHERE Table2.Key = Table1.Key)) > WHERE Key IN (SELECT Key FROM Table2) >as otherwise all the rows in Table1 without an entry in Table2 are updated >so that the values in Field1, Field3 are null -- not what was intended! >Yours, >Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> >}From: CRAIG@CHEMISTRY.CHEM.UTAH.EDU >}Date: Wed, 22 Nov 1995 9:01:28 -0700 (MST) >}X-Informix-List-Id: <list.8052> >} >}Viggo, >} >} I'm a bit fuzzy on your example, but I think you are looking for >}this: >} >} UPDATE table1 >} SET (field1, field3) = >} ((SELECT field1, field3 >} FROM table2 >} WHERE key = table1.key)) >} WHERE key = <anything you might want to add to narrow the update>; >} >} --Craig (craig@chemistry.utah.edu) >} >}}I want to update table1 with values from table2. >}} >}}Table1 (100000 records) >}}---------- >}}key unique >}}field1 >}}field2 >}}field3 >}} >}}Table2 (5000 records, all keys is known in table1) >}}---------- >}}key unique >}}field1 >}}field3 >}} >}}I could easily extract 5000 update statements to a file, on basis of >}}the records in table2. >}} >}}ex: update table1 (field1, field3) set (field1, field3) = >}} (select field1, field3 from table2 where key = unique_value) >}} where key = same_unique_value >}} update ...... unique_value2 .... >}}etc etc (5000 statements in all) >}} >}}but I don't like that solution.