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

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.