在重复键上给出无效令牌 [英] On duplicate key gives Invalid token

问题描述

我试图用表clientref中cdclient中的数字更新employee_migration中的name_code.

I am trying to update name_code from employee_migration by numbers from cdclient from table clientref.

 INSERT INTO employee_migration (name_code)
      Select cl.cdclient 
        From clientref cl 
  Inner Join employee_migration em  
          ON cl.client like upper(em.name)
          ON DUPLICATE KEY UPDATE name_code VALUES (cl.cdclient)

我收到此错误: 令牌无效.

I get this error: Invalid token.

Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 3, column 1.
ON.

推荐答案

如果您使用的是 Firebird 服务器(未说过，但看起来像您的错误文本)，则您有MERGE命令.

If you use Firebird server (which was not said, but which looks like by your error text) then you have MERGE command for it.

但是，如果您使用 Interbase 服务器，那么我不知道如何在该服务器上编写该语句，请查阅Interbase手册，然后:

However, if you use Interbase server, then I do not know how you can write that statement there, consult Interbase manuals then: http://docwiki.embarcadero.com/InterBase/2017/en/Statement_and_Function_Reference_(Language_Reference_Guide)

您可以使用服务->服务器属性和日志菜单在 IBExpert 中检查要使用的服务器.

You can check the server you work with in IBExpert using Services -> Server Properties and Log menu.

假设您使用的是Firebird 2.1版或更高版本

Assuming you use Firebird version 2.1 or newer

• http://en.wikipedia.org/wiki/Merge_(SQL)
• https://www .firebirdsql.org/file/documentation/reference_manuals/fblangref25-zh-CN/html/fblangref25-dml-merge.html

• http://en.wikipedia.org/wiki/Merge_(SQL)
• https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-merge.html

例如这样的东西:

MERGE INTO employee_migration dest
USING (
      Select cl.cdclient, em.ID
        From clientref cl 
  Inner Join employee_migration em  
          ON cl.client like upper(em.name)
      ) as src
ON dest.ID = src.ID -- or whatever your key columns are

WHEN MATCHED THEN UPDATE SET dest.namecode = src.cdclient

WHEN NOT MATCHED THEN INSERT (namecode, ID, ....)
    VALUES ( src.cdclient, ...., ...........)

但是，如果没有样本数据，您的请求似乎没有什么实际意义.

However without sample data your request seems of little practical sense.

您的join条件是cl.client like upper(em.name)-多对多":对于clientref中的每一行，employee_migration中可以有许多对应的行，反之亦然.

Your join condition is cl.client like upper(em.name) - which is "many to many": for every row in clientref there can be many corresponding rows in employee_migration and vice versa.

因此，您可能会将employee_migration as dest中的行与src查询中的许多候选行进行匹配和更新.

So you would probably be matching and updating rows in employee_migration as dest with MANY candidate rows from the src query.

• 按照SQL标准，它应该立即产生错误.
• 通过Firebird 2.x实施-它会依次进行这些更新，覆盖以前的更新，并且只有最后一个候选行的结果才会保留.

这篇关于在重复键上给出无效令牌的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！