CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-updateconditional-statements
SomeRandomDeveloper
SomeRandomDeveloper

Reputation: 489

Oracle Pl SQL, conditional in UPDATE statement

I want to create a stored procedure that's flexible in handling an update on multiple columns for a table, but where all or some of the values may or may not be provided.

Something like this for instance:

UPDATE some_table
SET

IF(I_COLUMN_1 is not NULL) THEN
   COLUMN_1 = I_COLUMN_1
END IF;

IF(I_COLUMN_2 is not NULL) THEN
   COLUMN_2 = I_COLUMN_2
END IF;

WHERE
SOME_KEY = I_SOME_KEY;

Obviously not right, but just to give you some pseudo code to portray my idea.

The only way i can think to do this now, is check for each variable, and run a separate update statement for each column which feels really lame.

Upvotes: 1

Views: 5050

Answers (3)

Sanjay Bharwani
Sanjay Bharwani

Reputation: 4839

Try the conditional update, Update with Case for conditions.

Here is the sample query

We are trying to update the salary from Employee table based on their designation and total experience.

 UPDATE Employee emp
SET emp_sal = (
  case 
    when emp.designation = 'SA' AND emp.totalExp > 5 then 5000
    when emp.designation = 'A' AND emp.totalExp > 3 then 3000
    else 15000
  end
)

Try the query with your specific requirement. Hope this helps.

Upvotes: 2

Joël Salamin
Joël Salamin

Reputation: 3576

Here is a proposition of query that can do the job:

UPDATE some_table
SET COLUMN_1 = NVL(I_COLUMN_1, COLUMN_1)
   ,COLUMN_2 = NVL(I_COLUMN_2, COLUMN_2)
WHERE SOME_KEY = I_SOME_KEY

I'm not very familiar with Oracle but in T-SQL i would use ISNULL() function to do the job and the equivalent in Oracle is the NVL() function.

Hope this will help.

Upvotes: 3

rcfmonarch
rcfmonarch

Reputation: 153

My first thought would be to manipulate the variables in PL/SQL before you execute the UPDATE statement. This is pseudo-code, but something like this:

I_COLUMN_1 [Datatype] := NVL2([Value of Incoming Parameter], [Value for Update if Not Null], NULL);
I_COLUMN_2 [Datatype] := NVL2([Value of Incoming Parameter], [Value for Update If Not Null], NULL);
UPDATE [some_table]
    SET COLUMN_1 = I_COLUMN_1, COLUMN_2 = I_COLUMN_2
    WHERE [some_key] = I_SOME_KEY;

Using the NVL2(expression, return-if-not-null, return-if-null) function will evaluate an expression, returning either a value if the expression is not null or a second value if the expression is null.

Evaluate the input, then update the table. :)

Upvotes: 2

Related Questions