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:

insert into t1 (id, c1, c2) values (:id, :c1, :c2)
update t1 set c1 = :c1, c2 = :c2 WHERE id = :id;

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.

Since Firebird 2.1, you can also use UPDATE OR INSERT statement.

UPDATE OR INSERT INTO t1 (id, c1, c2)
VALUES (:id, :c1, :c2);

It automatically uses primary key columns when available. If you don't have a primary key, or want to use some other columns to match the records, you can use the MATCHING clause:

UPDATE OR INSERT INTO t1 (id, c1, c2)
VALUES (:id, :c1, :c2)

Furthermore, if you need some more complex logic, you can also use the
MERGE statement. For detailed usage description, take a look at Firebird release notes or README.merge.txt file in docs/sql.extensions directory of your Firebird installation.

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.

All contents are copyright © 2007-2023 unless otherwise stated in the text.

Links   Firebird   News   FlameRobin   Powered by FB: Home Inventory   Euchre  
Add content   About  

 Installation and setup
 Backup and restore
 Connectivity and API
 Errors and error codes