0 0 Votes

Firebird: INSERT with ON DUPLICATE KEY UPDATE

Question by PC Control | 05/01/2018 at 15:46

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 an entry with id = 10 and val = 1 in 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.

That's exactly what I would like to do with Firebird. 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

NetGuy

Show Profile | Message
Avatar
0Best Answer
0 Votes

After all, even in Firebird you can do 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" from Firebird automatically uses the existing column(s) with PRIMARY KEY. If you do not have a PRIMARY KEY column or 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".

More about this topic can be found in the Firebird-FAQ.
05/01/2018 at 19:01

ReplyPositive Negative
Reply

Related Topics

Firebird: SELECT with LIMIT
Question | 1 Answer

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 articles on askingbox.com. How to do.