2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

Oracle Inserting or Updating a row through a procedure

I need to insert or update table data based on column condition.

Let us consider emp table.

If empno is not available in that table then insert it.

if empno is available and data also matched with new data then do nothing.

If empno is available but data doesn"t match then update it.

Through single query i am not able to do this functionality.

Please help me how can i write this in a procedure

userimage

For this you do not need a procedure. You can directly use merge statement.

It will insert if record is not available.

It will do nothing if there is  no change in old record and new record.

it will update when there is a change in data.

You can use like below.

MERGE INTO emp e

    USING test h

    ON (e.empno= h.empno)

  WHEN MATCHED THEN

    UPDATE SET e.address = h.address

  WHEN NOT MATCHED THEN

    INSERT (id, address)

    VALUES (h.emp_id, h.address);    


If you need this in a procedure only then you can create a procedure and include this statement in procedure.

It will be easy to understand.

Answer is