I have the following Query in SQL Server that does not seem to run in firebird.
我在SQL Server中有以下查询似乎不在firebird中运行。
UPDATE TABLE1
SET FIELD1 = XFER.FIELD2
FROM COMPANY
INNER JOIN TABLE2 AS XFER
ON TABLE1.FIELD1 = XFER.FIELD1 WHERE FIELD1 not like 'STRING1%'
How would I convert this to firebird? If I leave it as is it gives me the error "Token unknown" on the from.
我怎么把它转换成火鸟?如果我保留它,它会给我一个错误“令牌未知”的来自。
2 个解决方案
#1
1
I don't think Firebird allows updates with joins. You can do this with a correlated subquery:
我不认为Firebird允许使用连接进行更新。您可以使用相关子查询执行此操作:
UPDATE COMPANY
SET FIELD1 = (SELECT FIELD2
FROM XFER
WHERE COMPANY.FIELD1 = XFER.FIELD1
)
WHERE FIELD1 not like 'STRING1%' AND
EXISTS (SELECT 1 FROM XFER WHERE COMPANY.FIELD1 = XFER.FIELD1);
#2
2
The Firebird syntax of update
does not allow a from
clause. Instead you should use merge
:
更新的Firebird语法不允许使用from子句。相反,你应该使用合并:
merge into table1
using (select table2.field1, table2.field2
from company
inner join table2 on company1.id = table2.companyid -- made up condition missing in your question
where table2.field1 not like 'STRING1%'
) src
on table1.field1 = src.field1
when matched then
update set table1.field1 = src.field2
#1
1
I don't think Firebird allows updates with joins. You can do this with a correlated subquery:
我不认为Firebird允许使用连接进行更新。您可以使用相关子查询执行此操作:
UPDATE COMPANY
SET FIELD1 = (SELECT FIELD2
FROM XFER
WHERE COMPANY.FIELD1 = XFER.FIELD1
)
WHERE FIELD1 not like 'STRING1%' AND
EXISTS (SELECT 1 FROM XFER WHERE COMPANY.FIELD1 = XFER.FIELD1);
#2
2
The Firebird syntax of update
does not allow a from
clause. Instead you should use merge
:
更新的Firebird语法不允许使用from子句。相反,你应该使用合并:
merge into table1
using (select table2.field1, table2.field2
from company
inner join table2 on company1.id = table2.companyid -- made up condition missing in your question
where table2.field1 not like 'STRING1%'
) src
on table1.field1 = src.field1
when matched then
update set table1.field1 = src.field2