CODEWITHSUNDEEP
sqlfirebird
Catalina
Catalina

Reputation: 95

On duplicate key gives Invalid token

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.

Upvotes: 1

Views: 331

Answers (2)

Arioch 'The
Arioch 'The

Reputation: 16065

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

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)

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

Assuming you use Firebird version 2.1 or newer

For example something like this:

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.

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.

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

  • By SQL standard it should generate an immediate error.
  • By Firebird 2.x implementation - it would instead do those updates one after another, overwriting previous updates, and only the last candidate row would have their result persisted.

Upvotes: 5

IVO GELOV
IVO GELOV

Reputation: 14269

Firebird has a different syntax than MySQL

MERGE INTO employee_migration
  USING (Select cl.cdclient 
    From clientref cl 
    Inner Join employee_migration em ON cl.client like upper(em.name)) AS tmp
  ON employee_migration.name_code = tmp.cdclient
  WHEN MATCHED THEN UPDATE SET name_code = tmp.cdclient
  WHEN NOT MATCHED THEN INSERT (name_code) VALUES(tmp.cdclient)

UPDATE

As @arioch-the properly pointed out you need the MERGE command. My original solution was actually wrong.

Upvotes: 1

Related Questions