--- In firebird-support@yahoogroups.com, Lin XG <sbdlinxg@...> wrote:

> If anyone reading would care to comment, I would still
> like to know why the original query doesn't work.
>
> UPDATE Table_A SET
> Table_A.A = Table_B.A,
> Table_A.B = Table_B.B
> Table_A.C = Table_B.C FROM Table_A INNER JOIN ON
> Table_A.MASTER_KEY = Table_B.MASTER_KEY;
>
> I've used this type of query lots with MS Access and
> it works fine on Access tables and on linked Firebird
> tables.

Access SQL isn't SQL that's recognised by any standard - it's an SQL-
like layer over an old QBE engine and file-based table structures.
It's the way it is because the Jet engine is the way it is. MSSQL
(another largely non-standard-compliant SQL implementation) is aware
of how to interpret Access SQL for MSSQL purposes. The rest of the
world doesn't know what to make of it. You are bound for
disappointment if you hope that Access syntax will work in a standard
SQL engine such as Firebird. Your query doesn't work because it
simply isn't syntactically valid.

UPDATE Table_A SET <--- so far, so good
Table_A.A = Table_B.A, <--- invalid reference (needs a correlated
subquery)
Table_A.B = Table_B.B <--- as above, plus missing comma
Table_A.C = Table_B.C FROM Table_A INNER JOIN ON <--- more invalid
references)
Table_A.MASTER_KEY = Table_B.MASTER_KEY;

There is no supported syntax for retrieving multiple values as input
to an update specification via a single expression involving another
table. You need a scalar subquery (i.e. one that returns a single
value) to query the other table[s] independently for each input
value. To do this, even when *you* know that the values are coming
from the same source row, you use a *separate* table alias for
each "hit" on Table_B:

UPDATE Table_A aa
set A = (select bb.A from Table_B b1
where b1.MASTER_KEY = aa.MASTER_KEY),
B = (select b2.B from Table_B b2
where b2.MASTER_KEY = aa.MASTER_KEY),
C = (select b3.C from Table_B b3
where b3.MASTER_KEY = aa.MASTER_KEY)

Because inserts are not targeted at existing rows in the destination
table, you *can* perfom inserts using a non-scalar subquery, e.g.

INSERT INTO TABLE_A (MASTER_KEY, A, B, C)
SELECT MASTER_KEY, A, B, C FROM TABLE_B
WHERE MASTER_KEY = ? <--- a constant

Hope this is clearer for you...

./heLen