Firebird 2.5 update or insert into

Question

I can't figure out how to write a SQL statement in Firebird so it should add new row, and if some conditions meet to update also other column.

Example:

(in MySql looks like this:)

insert into 
table (col1, col2, col3) values ('a', 'b', 'c')
on duplicate key update col4=col3;

Answer 1

You have two options in Firebird

UPDATE OR INSERT

You can use UPDATE OR INSERT:

UPDATE OR INSERT INTO table (col1, col2, col3)
  VALUES ('a', 'b', 'c')
  MATCHING (col1, col2)

The MATCHING-clause is optional. If you leave it out, it will use the primary key:

UPDATE OR INSERT inserts a new record or updates one or more existing records. The action taken depends on the values provided for the columns in the MATCHING clause (or, if the latter is absent, in the primary key). If there are records found matching those values, they are updated. If not, a new record is inserted.

Contrary to the code in your question, this will not allow you to customize the update: it will use the same values as would be inserted.

If you want more control, use merge

MERGE

The MERGE statement gives you more control, but can be a bit more verbose:

MERGE INTO table AS t
  USING (select 'a' as new1, 'b' as new2, 'c' as new3 from rdb$database) as s
    ON t.col1 = s.new1 and t.col2 = s.new2
  WHEN MATCHED THEN 
    UPDATE SET t.col4 = t.col3
  WHEN NOT MATCHED THEN 
    INSERT (col1, col2, col3) values (s.new1, s.new2, s.new3)

However contrary to UPDATE OR INSERT, MERGE can't use the primary key implicitly. You will need to specify the duplicate rules yourself in the ON clause.