Assume a table structure of MyTable(KEY, datafield1, datafield2...)
.
Often I want to either update an existing record, or insert a new record if it doesn't exist.
Essentially:
IF (key exists)
run update command
ELSE
run insert command
What's the best performing way to write this?
Best Answer
don't forget about transactions. Performance is good, but simple (IF EXISTS..) approach is very dangerous.
When multiple threads will try to perform Insert-or-update you can easily get primary key violation.
Solutions provided by @Beau Crawford & @Esteban show general idea but error-prone.
To avoid deadlocks and PK violations you can use something like this:
or