Hi,

you might use a stored proc/execute block:

select F1, F2, F3, ... from Account where ref = '10976' into :f1, :f2,
:f3, ...;
update trans set F1 = :f1, F2 = :f2, F3 = :f3, ... where to_account =
'10976';

Lafras Henning a écrit :

>
> 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
> <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
>
>