What is the correct/ fastest way to update/insert a record in sql (Firebird/MySql)

Question

I need some SQL to update a record in a database if it exists and insert it when it does not, looking around there looks to be several solutions for this, but I don't know what are the correct/ accepted ways to do this.

I would ideally like it to work on both Firebird 2 and MySQL 5 as the update will need to be ran against both databases, and it would be simpler if the same SQL ran on both, if it worked on more database that would be a plus.

Speed and reliability also factor in, reliability over speed in this case but it will potentially be used to update 1000's of records in quick succession (over different tables).

any subjections?

Answer 1

In Firebird 2.1 you can use UPDATE OR INSERT for simple cases or MERGE for more complex scenarios.

Answer 2

You should either use something like this:

BEGIN TRANSACTION
IF EXISTS (SELECT * FROM the_table WHERE pk = 'whatever')
    UPDATE the_table SET data = 'stuff' WHERE pk = 'whatever'
ELSE
    INSERT INTO the_table (pk, data) VALUES ('whatever', 'stuff')
COMMIT

Or this, but send them separately and ignore any errors from the INSERT about violating primary key constraints:

INSERT INTO the_table (pk, data) VALUES ('whatever', 'stuff')

UPDATE the_table SET data = 'stuff' WHERE pk = 'whatever'