what is better (if) or (where) in sql?

Question

In a stored procedure, I want to update a large data set based on some conditions, So what is better:

Scenario 1 (one select multiple if)

Select All Data records then 
LOOP

IF (Condition1 == true)
  execute update statement
IF (Condition2 == true)
  execute update statements
END LOOP

Scenario 2 (multiple selects with where)

Select Data where (condition 1) loop
   execute update statements
end loop

Select Data where (condition 2) loop
  execute update statements
end loop

If it is the same table, why not just one update? If the number of rows to update is small compared to the size of the table, the first approach is surely worse. If you can show us the actual statement, maybe we can help write just one update — gsalem, Jan 09 '20 at 07:48
You shouldn't be doing slow-by-slow updates in a loop to begin with. Use `update ... where condition1` and `update ... where condition2` — , Jan 09 '20 at 07:49
It is impossible to give an answer which applies to all possible cases. The closest thing to a universal answer is that executing individual DML statements inside a SELECT driven loop is usually an anti-pattern. SQL is a **set-oriented** language, so doing things in sets is usually the most efficient way of achieving anything. — APC, Jan 09 '20 at 07:51
If you have a specific scenario please [read this thread on asking Oracle tuning questions](https://stackoverflow.com/a/34975420/146325). — APC, Jan 09 '20 at 07:53

score 2 · Answer 1 · answered Jan 09 '20 at 07:49

I don't think there is an answer valid in all scenarios (database products, select query, input size etc), but keep in mind :

1) Your solution based on IF will have only ONE select AND one LOOP, so if the system lose more time on selecting the records (es very complex query) maybe better going for one loop.

2) Your solution based on WHERE instead as ONE query for every conditions, so I would avoid it for more than 2 or 3 conditions unless it's very very fast query (es. selecting record from a configuration table with 100 records or so).

3) Plus keep in mind you could place the IF directly in the UPDATE statement.

In general I would go for IF (1) or (3) solutions.

Regards

score 1 · Answer 2 · answered Jan 09 '20 at 08:24

Another method is to use a MERGE statement then you don't need to use loops or separate SELECT and UPDATE statements:

MERGE INTO destination dst
USING (
  SELECT *
  FROM   source
  WHERE  condition = True
) src
ON ( dst.id = src.id )
WHEN MATCHED THEN
  UPDATE SET column = src.column;

what is better (if) or (where) in sql?

2 Answers2