I need to do an update under certain circumstances in SQL. It is an update of a single element in a table. Which of these options will consume more resources.

1. Ask first if the update would be necessary by means of a select and then do the update or
2. execute the query so that the update is carried out despite the fact that in some cases it will overwrite the same values.

CodePudding user response：

there is a time gap in your option 1. it is theoretically possible to select to find no issue - then someone else does an update - then you do an update thinking it is safe..

option 2 does not have this issue.

CodePudding user response：

There is a third option: do an update selectively.

For example, let's say you want to reduce a balance by an amount, but only if the balance is greater than the amount.

You could create a query like this:

UPDATE myTable SET balance = balance - :amount 
   WHERE accountID = :accountId AND balance >= :amount

Then check ROW_COUNT() to see if the update succeeded. (PHP and other languages have specific functions for this)

This has the specific benefit that it avoids the race condition implicit in the SELECT - UPDATE approach.