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.

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)
MATCHING (id);

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.

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.



All contents are copyright © 2007-2017 FirebirdFAQ.org unless otherwise stated in the text.


Links   Firebird   News   FlameRobin   Home Inventory powered by FB  
Add content   Advertise   About  

Categories
 Newbies
 SQL
 Installation and setup
 Backup and restore
 Performance
 Security
 Connectivity and API
 HOWTOs
 Errors and error codes
 Miscellaneous