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

For MySQL, try the REPLACE command: http://dev.mysql.com/doc/refman/5.0/en/replace.html

(Please see the comment on this answer by Milan Babuskov for equivalents on Firebird.)

Answer 2

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Syntax :

REPLACE [LOW_PRIORITY | DELAYED] [INTO] tbl_name [(col_name,...)]

{ VALUES | VALUE} 

({expr | DEFAULT},...),(...),...

Whereas : It is best to avoid REPLACE when working with constraints.

Answer 3

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'

Answer 4

I've used INSERT in MySQL to update rows with:
INSERT INTO table () VALUES () ON DUPLICATE KEY UPDATE key
But you can't use an auto generated key.

Answer 5

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

Answer 6

In firebird before 2.1 you can use this tricky way:

insert into table (id, a, b, c) values (:id, :a, :b, :c)
when SQLCODE -803 
do
begin
  update table set a = :a, b = :b, c = :c where id = :id;
end;