Not valid for Firebird or not valid according to the SQL specifications?


-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: Tuesday, July 12, 2005 2:35 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Update on joined tables

At 05:44 AM 12/07/2005 +0000, you wrote:

>Hi everyone,
>
>Is there a way to update a table A with values from another table B
>when the two tables share same field value?
>I've tried this, but it doesn't work:
>
>update tableA
>inner join tableB on tableA.field1 = tableB.field1 set tableA.field2 =
>tableB.field2

Correct, it's not a valid statement. You'll need a correlated subquery:

update tableA a
set a.field2 =
(select b.field2 from TableB b
where b.field1 = a.field1)

Watch out, though. This will except with "Multiple rows in singleton
select" if the subquery doesn't return a scalar result, i.e. there is
more than one row in b where b.field1 = a.field1. (The same limitation
of logic potentially exists in your posited join syntax, too...)

./heLen




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

Visit http://firebird.sourceforge.net 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