Hi All

I have a large number of fields (30+) that need to be copied from one
table to another, at the moment I use the following code which seems
inefficient:

update Trans
set
TRANS.legal_name=(SELECT account.LEGAL_NAME FROM Account WHERE
account.ref=trans.to_account )
, TRANS.Del_1=(SELECT account.Del_1_1 FROM Account WHERE
account.ref=trans.to_account )
, TRANS.Del_2=(SELECT account.Del_1_2 FROM Account WHERE
account.ref=trans.to_account )
, TRANS.Del_3=(SELECT account.Del_1_3 FROM Account WHERE
account.ref=trans.to_account )
, TRANS.Del_4=(SELECT account.Del_1_4 FROM Account WHERE
account.ref=trans.to_account )
where (Trans.Ref='10976' )

Is there a better method than this in Firebird?


I found this article: http://www.cryer.co.uk/brian/sql/sql_crib_sheet.htm
That describes other methods, but they don't work in firebird:

This works in MS-Access and MySQL (5) but not in SQL Server:
update TableOne
inner join TableTwo on TableOne.commonID = TableTwo.commonID
set TableOne.field1 = TableTwo.fieldX
or
This works in MS-Access but not in SQL Server:
update TableOne, TableTwo
set TableOne.field1 = TableTwo.fieldX
where TableOne.commonID = TableTwo.commonID
or
This works in SQL Server but not in MS-Access (my thanks to John Lee for
this):
update tableOne
set tableOne.field1=tableTwo.fieldX
from tableOne, tableTwo
where tableOne.commonID=tableTwo.commonID


Regards
Lafras