What is the correct / fast way to update / insert records in sql (Firebird / MySql)

Question

I need some kind of SQL to update the record in the database, if it exists, and to insert it when it does not, looking around, it seems there are several solutions for this, but I do not know what the correct / acceptable ways to do it .

I would ideally want it to work with both Firebird 2 and MySQL 5, since the update needed to be run for both databases, and it would be easier if the same SQL worked on both, if it worked on a larger database which would be a plus.

In this case, speed and reliability also depend on reliability and speed, but it can potentially be used to quickly update 1000 records (in different tables).

any submissions?

Answer 1

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 INSERT about violation of primary key restrictions:

 INSERT INTO the_table (pk, data) VALUES ('whatever', 'stuff') UPDATE the_table SET data = 'stuff' WHERE pk = 'whatever' 

Answer 2

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

Answer 3

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

(See comment on this answer by Milan Babuskov for equivalents on Firebird.)

Answer 4

In firebird prior to 2.1, you can use this complicated 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; 

Answer 5

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

Syntax:

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

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

While it is better to avoid REPLACE when working with constraints.

Answer 6

I used INSERT in MySQL to update rows with: INSERT INTO table () VALUES () ON DUPLICATE KEY UPDATE key
But you cannot use an automatically generated key.