On duplicate key gives Invalid token

Question

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.

`select` inside is working. I get numbers but I have to update them in the table — Catalina, Jul 23 '18 at 10:18
@lucumt, I found an example here: https://stackoverflow.com/questions/45366136/insert-into-select-on-duplicate-mysql-query — Catalina, Jul 23 '18 at 10:20
Maybe it's because the 'ON DUPLICATE' comes right after the inner join, can you try putting a "WHERE 1=1' for ex between the join and the on duplicate? — Bert Maurau, Jul 23 '18 at 10:30
The error isn't a MySQL error it looks like you are trying a execute a MySQL syntax query on a other DBMS — Raymond Nijland, Jul 23 '18 at 10:30
I think it's a little conflict between inner join and on duplicate — Catalina, Jul 23 '18 at 10:34
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-merge.html — Arioch 'The, Jul 23 '18 at 11:03

Arioch 'The · Accepted Answer · 2018-07-23T11:23:31.100

5

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.

edited Jul 23 '18 at 11:23

answered Jul 23 '18 at 11:18

Arioch 'The

15,799
35
62

Can I use the code you wrote? if yes, I have this error: Column unknown. SRC.CLIENT. – Catalina Jul 23 '18 at 11:35
You have to understand the **example** I drafted and complete it, because I obviously do not know your tables and their columns. More so, I have doubts that your request has a practical sense, cause it seems to updates every row **randomly** picking one of many candidates... That being said, my code does not have `SRC.CLIENT` in it. – Arioch 'The Jul 23 '18 at 11:36
If I create two small tables: table 1 (name, code1) and table 2 (name,code2). How to copy code1 from table1 to code2 in table 2. In general, I mean this – Catalina Jul 23 '18 at 11:41
then you probably have "name" as `PRIMARY KEY` in both table. Well, first of all you have to decide what are you planning to do with values in table1 with the name that are missed from table2? Ignore them, insert them, copy them to table3, whatever. That has to be decided. Then, if you want to match the tables on `table1.name = table2.name` then that is what you have to do. Where did you got that `ON cl.client like upper(em.name)` from? why not `cl.client = em.name` ? Do you understand what `UPPER` and `LIKE` do there? They introduce "many to many" instead of "one to one" – Arioch 'The Jul 23 '18 at 11:45
For the utterly simplified example `table_source( ID, value )` and `table_destination( ID, value )` and strict matching rule `ID = ID` you can write `MERGE` by the documentation or wiki or by any googled example. It is just that your question looks very different from that "two small tables" example. – Arioch 'The Jul 23 '18 at 11:47
Ok, I understood you. I did a query which works (no errors), but it doesn't update anything. I think as you said the problem is in "many to many". My working day is over and tomorrow I'll try to use Generators to autoincrement ids to do iterations in my table. Hope, it will be done. I'll write about my result tomorrow and make your answer best)) – Catalina Jul 23 '18 at 12:51
Hopefully you did not forget to COMMIT transaction after the query? Also, IBExpert on FB shows how many rows were updated after the query. There may be different cases: no rows updated, or many rows updated with the same data, that they already had – Arioch 'The Jul 23 '18 at 12:59
Alternatively, use [`UPDATE OR INSERT`](https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-update-or-insert.html) (although that isn't usable for this specific case). – Mark Rotteveel Jul 23 '18 at 15:29
@MarkRotteveel Ivo Gelov exactly suggested it (check his initial answer edition). It can be made working, if to put it for the goal... Just the main problem is we are not sure what the topic starter wants and I wonder if she knows it surely herself... No tables, not input data, no output data, not even FB version.... – Arioch 'The Jul 23 '18 at 17:33
Glad to hear! Both done the job and hopefully learned something new :-D – Arioch 'The Jul 24 '18 at 12:43

IVO GELOV · Answer 2 · 2018-07-23T11:19:47.047

1

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.

edited Jul 23 '18 at 11:19

answered Jul 23 '18 at 10:37

IVO GELOV

13,496
1
17
26

Hm, probably it works with VALUES() and refuses with SELECT ... Then you need a trigger to silently convert the failing INSERTs into UPDATEs. – IVO GELOV Jul 23 '18 at 10:45
`THEN INSERT (name_code)` - would not do, you would have to have ALL columns for the SQL INSERT statements ( except for auto-generated or auto-computed ones ) – Arioch 'The Jul 23 '18 at 11:25
Another trick would be to use `EXECUTE BLOCK` for making "anonymous temporary procedure", there you run `FOR SELECT` loop and execute that very `UPDATE OR INSERT` for every row in the loop. Though usually still better to write correct merge. https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-execblock.html & https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-coding.html#fblangref25-psql-forselect – Arioch 'The Jul 23 '18 at 11:28
Completely agree with you. I have not worked with Firebird for a while (I am using Postgres) so I always wonder how some very simple things are made in such a complex and inconvenient manner ... – IVO GELOV Jul 23 '18 at 12:03

On duplicate key gives Invalid token

2 Answers2

UPDATE

Linked