CODEWITHSUNDEEP
oracle-databaseplsqlif-statement
Matt
Matt

Reputation: 2843

Update table with if statement PL/SQL

I am trying to do something like this but am having trouble putting it into oracle coding.

BEGIN
IF ((SELECT complete_date FROM task_table WHERE task_id = 1) IS NULL)
THEN
 UPDATE task_table SET complete_date = //somedate WHERE task_id = 1;
ELSE
 UPDATE task_table SET complete_date = NULL;
END IF;
END;

But this does not work. I also tried

IF EXISTS(SELECT complete_date FROM task_table WHERE task_id = 1)

with no luck.

Upvotes: 3

Views: 14270

Answers (2)

Gigatron
Gigatron

Reputation: 2055

What is the intention of this part?

IF ((SELECT complete_date FROM task_table WHERE task_id = 1) IS NULL)

Is it to find if there are rows where complete_date is null and task_id = 1?

Or is it to check if there are no rows where task_id = 1?

Upvotes: 0

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18818

I don't think you'd need the procedural block if your actual logic is like the one above.

Assuming this is your task:

"if the value of complete_date for id 1 is NULL, update it with XXX. Otherwise set it to null".

You could just run ...

Update task_table
  set complete_date = nvl2(complete_date,NULL, <**your date**>)
  where task_id = 1;

This will only update those records where the complete_date is null with your new date.

Upvotes: 8

Related Questions