Update and insert in oracle PL/SQL along with if else condition

Question

Please find by below code snippet :

BEGIN
  IF (in_config1 IS NOT NULL OR in_config1 !='') THEN
      UPDATE question_table
      SET comment = in_config1 
      WHERE id= id
      AND questionid = 1;
  ELSE
      INSERT INTO question_table(
      tid
      ,questionid 
      ,comments) 
      VALUES( id
      , 1
      , in_config1);
 END IF;
 END;

My requirement is to update question_table based on some condition.If update fails which would be incase if record is not present,then i need to add insert statement in the else block. In the above code update is working. But insert statement is not getting executed.Please let me know whats wrong?

Are you getting any errors? What sample data have your tried? — Hawk, Jun 21 '15 at 08:45
No am not getting any errors. Basically i wnat to modify it into upsert statement. If row matched then update row else insert row — nutty, Jun 21 '15 at 08:46

score 4 · Accepted Answer · answered Jun 21 '15 at 09:12

If I understand you, you need upsert statement, where you update if the record match some value, and you insert if it doesn't. The best option can serve you in this case is MERGE clause. It's efficient, flexible and readable. The following is a general script that might need minor changes based on where you are getting the values from and your tables structures.

MERGE INTO question_table a   
USING (SELECT id, your_key, in_config1 FROM DUAL) b   
ON (a.id = b.id)   
WHEN MATCHED THEN   
UPDATE question_table   
SET comment = in_config1   
WHEN NOT MATCHED THEN   
INSERT INTO question_table(   
      tid  
     ,questionid    
     ,comments)   
     VALUES( id  
     , 1  
     , in_config1);

score 0 · Answer 2 · answered Jun 22 '15 at 09:06

simply you can do like this use sql%notfound

  BEGIN
    IF (in_config1 IS NOT NULL OR in_config1 != '') THEN
      UPDATE question_table
         SET comment = in_config1
       WHERE id = id
         AND questionid = 1;
      if sql%notfound then
        INSERT INTO question_table (tid, questionid, comments) VALUES (id, 1, in_config1);
      end if;
    END IF;
  exception
    when others then
      dbms_output.put_line(sqlerrm);
  END;

Update and insert in oracle PL/SQL along with if else condition

2 Answers2