找到你要的答案

Q:Cross table update in Firebird SQL

Q:Firebird SQL交叉表的更新

Maybe somebody knows how to do that. In MySQL I would do as follows:

UPDATE T1
SET T1.c2 = T2.c2
WHERE T1.c1 = T2.c1 

Unfortunately this results in an error when trying this in Firebird and yields the following response:

SQL Message : -204
Undefined name

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -204
Table unknown
T1
At line 2, column 1

Somebody an idea?

也许有人知道该怎么做。在MySQL中我会做如下:

UPDATE T1
SET T1.c2 = T2.c2
WHERE T1.c1 = T2.c1 

不幸的是,这个结果在一个错误在这火鸟和产生以下的反应:

SQL Message : -204
Undefined name

Engine Code    : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -204
Table unknown
T1
At line 2, column 1

有人有主意吗?

answer1: 回答1:

Firebird UPDATE statements can only reference a single table. To do what you want you have two options.

Using a correlated subquery:

UPDATE T1
SET c2 = (SELECT T2.c2 FROM T2 WHERE T2.c1 = T1.c1)

or using MERGE:

MERGE INTO T1
USING T2
ON T1.c1 = T2.c1
WHEN MATCHED THEN
   UPDATE SET T1.c2 = T2.c2

火鸟更新语句只能引用一个表。做你想要的你有两个选择。

使用相关子查询:

UPDATE T1
SET c2 = (SELECT T2.c2 FROM T2 WHERE T2.c1 = T1.c1)

或使用合并:

MERGE INTO T1
USING T2
ON T1.c1 = T2.c1
WHEN MATCHED THEN
   UPDATE SET T1.c2 = T2.c2
answer2: 回答2:

You need to mention both tables before you access them.

Try:

UPDATE T1, T2
SET T1.c2 = T2.c2
WHERE T1.c1 = T2.c1 

Or alternatively

update t1
  inner join t2
    on t1.c1 = t2.c1
  set t1.c2 = t2.c2

edit

apparently firebird doesn't support joins in updates, so try:

update t1
  set t1.c2 = (select c2 from t2 where c1 = t1.c1);

您需要在访问它们之前提及两个表。

尝试:

UPDATE T1, T2
SET T1.c2 = T2.c2
WHERE T1.c1 = T2.c1 

或者

update t1
  inner join t2
    on t1.c1 = t2.c1
  set t1.c2 = t2.c2

edit

显然,火鸟不支持加入更新,所以尝试:

update t1
  set t1.c2 = (select c2 from t2 where c1 = t1.c1);
mysql  firebird