查询从SQL Server到firebird的转换（带内连接的UPDATE）

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

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

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