Thanks,
I have expanded the sugestion to the following...
but it seems overly verbose, is this the best way?

set term !! ;
EXECUTE BLOCK as
DECLARE VARIABLE f1 VARCHAR(100);
DECLARE VARIABLE f2 VARCHAR(100);
DECLARE VARIABLE f3 VARCHAR(100);
DECLARE VARIABLE f4 VARCHAR(100);
DECLARE VARIABLE f5 VARCHAR(100);
begin
SELECT
LEGAL_NAME,
Del_1_1 ,
Del_1_2,
Del_1_3,
Del_1_4
FROM ACCOUNT where ref='10218'
into :f1,:f2,:f3,:f4,:f5;
update trans
set
legal_name=:f1,
Del_1_1 =f2,
Del_1_2 =:f3,
Del_1_3 =f4,
Del_1_4 =:f5
where ref='10200';
end!!
set TERM ; !!


Teträm Corp wrote:

>
> 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>
> > <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
> >
> >
>
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.516 / Virus Database: 269.20.8 - Release Date: 2008/02/19 12:00
>