CODEWITHSUNDEEP
sqloracle-databaseperformanceplsql
Hala Abdulla
Hala Abdulla

Reputation: 1

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

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

Upvotes: 0

Views: 117

Answers (2)

Matteo
Matteo

Reputation: 487

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.

Upvotes: 2

MT0
MT0

Reputation: 168232

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;

Upvotes: 1

Related Questions