Hi I have a table called Task_Status as below.
Here I have to check if the Task is already present in the table then
have to update the status to InActive for all the records and insert the records as status Active as below.
To my knowledge insert is not supported under update statement in merge, do we have any better approach. Of course we can do this by two sqls, but want this to be achieved through merge.
CREATE TABLE Task_Status
(Task_id NUMBER(10) PRIMARY KEY,
Request_Id NUMBER(10) not null,
Task_Status VARCHAR2(100)
);
MERGE INTO Task_Status Tgt
USING (SELECT 100 Task_id, 'Req_100' Request_Id , 'Active' Status FROM Dual ) Src
ON (Src.Task_Id=Tgt.Task_Id)
WHEN MATCHED THEN
UPDATE SET Status='InActive'
insert (Task_Id, Request_id, Status)
VALUES(Src.Task_Id, Src.Request_id, Src.Status)
WHEN NOT MATCHED THEN
insert (Task_Id, Request_id, Status)
VALUES(Src.Task_Id, Src.Request_id, Src.Status);
I am using Oracle 19C.
Thanks in advance !!