Sure I admit it would occasionally be handy if there were a set operator that could be used with the UPDATE statement, like:

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

Though I've no knowledge of the SQL standard or Firebird 2.1. I think that sticking to the SQL standard is more important than making handy solutions, and there is likely to be better ways to implement something like this than what my mind thinks sound sensible after thinking about it for two minutes (not to mention the complexity that I've ignored considering ;o).

And I am happy with the current state of Firebird,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Lafras Henning
Sent: 24. februar 2008 18:48
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Copying many fields between tables

Thanks for the thread link, at least I was not to far wrong with my
initial attempt.

Fortunately my requirement is only for a single record copy, so the
performance wont be much of an issue.

But you must agree that in modern times with the sophistication of SQL
it is strange that there is not an easier standard means to copy a
couple of fields.

Thanks all
Lafras

Svein Erling Tysvaer wrote:

>
> According to what Ann Harrison wrote in a message 20 August last year
> (http://tech.groups.yahoo.com/group/firebird-support/message/88476
> <http://tech.groups.yahoo.com/group/firebird-support/message/88476>), it
> may look ugly, but isn't as slow as it may seem. Milan said he didn't
> quite agree, whereas I have no idea who's right (both Ann and Milan are
> very trustworthy and knowledgeable). So, have you tested and compared
> the difference between updating one field and thirty fields this way?
>
> Set
>
> Lafras Henning wrote:
> > 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
>
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.516 / Virus Database: 269.20.8/1288 - Release Date: 2008/02/19 08:47
>

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links