Problem: How can I update multiple fields in table1 with fields from table2? Solution: *********************************************************************************** Note: The information in this document refers to all versions of InterBase. *********************************************************************************** The standards compliant ways to do that are either: Update table1 set table1.f1 = select t2.f1 from t2 where t2.key = table1.key, table1.f2 = select t2.f2 from t2 where t2.key = table1.key That's not very efficient, though, if t2.key is unique and indexed, which it should be. The overhead of the second select is minimal. Everything needed to resolve the query is waiting in memory. Alternately, for languages which support cursors, you would declare a cursor on t2 that retrieves the desired values and the key value, then execute an update statement like this: update table 1 set f1 =, f2 = where key = If t2 is a small lookup table and you want to preselect the rows from table1, you can open a cursor on table1 for update, execute a separate select on t2 to get the desired fields, then do an update where current of cursor. Finally, a non-standard way that is the most efficient for InterBase. Create a stored procedure like this - assuming that pk is the primary key of table1: for select table1.pk, t2.f1, t2.f2 from table1, t2 where table1.key = t2.key into :v_pk, :v_f1, :v_f2 do begin update table1 set f1 = :v_f1, f2 = :v_f2 where table1.pk = :v_pk end; source: Ann Harrison on listserv
Last Modified: 26-OCT-00