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
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.