In a software that I'm working, I have a table with a few columns and a id. In some events in my software, I need to update some column of a record with a certain unique field (other column), but sometimes the value that i'm supposed to update is the same in the table, it have been updated by a previous event, so i just need get the key in my software. When the key doesn't exist yet, i need to insert.
So, what is better in my software?
1 - First try update, searching by the unique field (with returning into clause to get the primary key), if the key exists, it will works even if the update wasn't really needed, i.e, if the value updated is the same that the older. But, if the key doesn't exist yet, i would get a error, so I do the
INSERT (and returning into) with the correct values. I never perform a
SELECT statement in this option.
2 - First search the record with a select statement (
where certain_unique_column = certain_val_in_event) , i just try a update if the key exists and if the new value is different from the old value. If the key doesn't exist, I just do
An other option would be try the 'INSERT' first, but i guess this is worse alternative, because for something like 4 in 5 queries, the key was already insert before. And I need increment a sequence when i try to insert (passing mysequence.nextVal), so i would do unnecessary increments in 4 in each 5 queries.