Update table with if statement PL/SQL

Question

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.

Solution

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.

Comments

[Matt]

But also if it is not Null then make it null

[Matt]

If there is a complete date there then the user must want to mark it incomplete so place null there. If there is no complete date then the user must want to mark it complete so place a date there.