22 Votes

Firebird: INSERT with ON DUPLICATE KEY UPDATE

Question by PC Control | Last update on 2023-11-01 | Created on 2018-01-05

I would like to either insert some data into a Firebird database or, if the corresponding ID already exists, update the record with that ID. I would like to do this within one query so that I do not get any error messages and I do not have to check first if an ID is already in the table.

For this purpose, MySQL has the command "INSERT ... ON DUPLICATE KEY UPDATE", which is constructed as follows:

INSERT INTO tab (id, val) VALUES (10, 1)
  ON DUPLICATE KEY UPDATE val = val + 1;

This inserts a record with id = 10 and val = 1 into the table "tab", if the id = 10 does not already exist. However, if this ID exists, the value of val of this row is incremented by 1 instead.

That's exactly what I would like to do with Firebird now, too. Unfortunately, Firebird does not seem to understand this syntax and always gives me only an error message. What can I do? Is this option not available in Firebird?

ReplyPositiveNegative
Avatar NetGuy
2Best Answer2 Votes

Even in Firebird you can perform an "ON DUPLICATE KEY UPDATE" INSERT. However, in Firebird the syntax is a bit different. Instead of the MySQL syntax, you have to write "UPDATE OR INSERT".

Your query from above would look like this in Firebird:

UPDATE OR INSERT INTO tab (id, val) 
  VALUES (10, 1);

The "UPDATE OR INSERT" query from Firebird automatically uses the existing column(s) with PRIMARY KEY. If you do not have a PRIMARY KEY column or if you want to use other columns for the match, you can use the keyword MATCHING at the back of your query:

UPDATE OR INSERT INTO tab (id, val) 
  VALUES (10, 1) 
  MATCHING(id)

After MATCHING in the bracket, you define which columns are to be used for the match. In this example, I took again the column "id" in order to correspond to your example.

More about this topic can be found in the Firebird-FAQ.
Last update on 2023-11-01 | Created on 2018-01-05

ReplyPositive Negative
Reply

Related Topics

MySQL: Write current Date or Time into Column

Tutorial | 0 Comments

Firebird: SELECT with LIMIT

Question | 1 Answer

Delphi/Lazarus: Is the ALT, SHIFT or CTRL key pressed?

Tutorial | 0 Comments

Delphi: System-Wide HotKey

Tutorial | 1 Comment

jQuery: Assign Action to Keyboard Keys (Keyboard Event)

Tip | 0 Comments

Delphi/Lazarus: Delete selected Items from ListBox using DEL-Key

Tip | 3 Comments

Delphi/Lazarus: Only allow Numbers in TEdit

Tip | 0 Comments

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.