How to do INSERT OR UPDATE depending of existence of primary key value?

You can do it in a stored procedure or trigger with Firebird 1.x, and anywhere in Firebird 2.x (using EXECUTE BLOCK). The query looks like this:

BEGIN
insert into t1 (id, c1, c2) values (:id, :c1, :c2)
WHEN SQLCODE -803 DO
update t1 set c1 = :c1, c2 = :c2 WHERE id = :id;
END

Field ID is the primary key in above example. It's quite simple: it tries to insert and if PK violation happes, it updates the record.

Do you find this FAQ incorrect or incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page.

If you are a commercial tool maker and your tool features a great way to handle the issue written about in this FAQ, please check out our advertisement page.