1

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.

OR

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 INSERT.

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.

  • Check out this answer. The question was for SQLServer but I believe the concepts are relevant. – Erik Aug 17 '15 at 18:05
  • 1
    Neither 1 nor 2. Just add a condition to your where clause in your update so that it only updates something if the column has not that value, e.g. update ... where (...your existing where conditions...) and certain_unique_column <> certain_val_in_event – a_horse_with_no_name Aug 17 '15 at 18:12
  • What is the approximate frequency of each occurring ? So how often do you get to the data, and it's already updated ? how often do you have to Add because it's not there ? The frequency can affect the final answer: If you're adding most of the time, I'd say make sure a constraint (if possible) is on there, try to INSERT, catch exception, do Update. If you're expecting it to be there most of the time, do the UPdate first (with the condition in WHERE as "a_horse_with_no_name" mentions) and check if you actually did update anything .. doing INSERT in the (rare) case you didn't. – Ditto Aug 17 '15 at 18:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.