community.borland.com

Article #25562: How to Update a column in a Table w/ values from another table

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