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

vs-data@inet.uni-c.dk wrote: : Hi, : : 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. : : How can I solve this problem in few or mabee one statement. : : Thanks in advance for your help. : : Viggo : : Hi Viggo, there are several ways to do this. The first (and most obvious) is writing a simple piece of code, (e.g. Stored Proceadure, 4GL), doing a FOREACH-loop. But there is an easier way (which maybe does not provide good performance) within a single SQL-statement: update table1 set (field1, field3) = (select field1, field3 from table2 where key = table1.key) where key in (select key from table2) I've tried this with two small tables - it worked! BTW, the WHERE-clause of the UPDATE (where key in ...) is necessary. If it is missing, every row of table1 will be updated, wich NULL-values for each key not existent in table2. Hope this helps, Peter