Menu
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?
653
Insertion is inserting a new key and update is updating the value of an existing key. If that is the case (a very common case) , update would be faster than insertion because update involves an indexed lookup and changing an existing value without touching the index.
Insertion would be faster, update takes more time because you have to be precise and accurate when deciding where and what part of the sql code will be updated. I think inserts will run faster. They do not require a lookup (when you do an update you are basically doing the equivalent of a select with the where clause).
Originally Answered: In SQL, what takes more time, insert or update ? In a way yes, update will take more time. This is only a case when there is a huge amount of data. As in this, first required row is to be found and then data needs to be updated.
The INSERT OR UPDATE command is an extension of the INSERT command, with these differences: If the row being inserted does not exist, INSERT OR UPDATE performs an INSERT operation. If the row being inserted already exists, INSERT OR UPDATE performs an UPDATE operation, updating the row with the specified column values.
In Firebird 2.1 you can use UPDATE OR INSERT for simple cases or MERGE for more complex scenarios.
179
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
