CODEWITHSUNDEEP
oracle-databasemergesql-deleteupsertnotin
Sakshi
Sakshi

Reputation: 31

How to use delete while using merge to update and insert query in oracle?

I am working with both PostgreSQL and oracle for same query. while updating using PostgreSQL, remaining entries gets delete and whichever matched get update. Same query using merge in oracle didn't delete non-updated entries.

Postgres query:

<pre><code>
 `INSERT INTO IAG_PERMISSION(id,bid,aid,s_type,subject,r_type,object,permissions,tid,person)
  VALUES(?,?,?,?,?,?,?,?,?,?)
  ON CONFLICT ON CONSTRAINT IAG_PERM_UK1
  DO  
  UPDATE SET SUBJECT=?,PERMISSIONS=?`
</code></pre>

Oracle Query:

<pre><code>
`MERGE INTO IAG_PERMISSION iap
 USING dual
 ON (iap.AID    = ?
 AND iap.R_TYPE = ? 
 AND iap.OBJECT = ?  
 AND iap.R_TYPE = ? 
 AND iap.PERSON = ? )
 WHEN MATCHED THEN UPDATE SET SUBJECT = ? , PERMISSIONS = ? 
 WHEN NOT MATCHED THEN INSERT (iap.id,iap.bid,iap.aid,iap.s_type,iap.subject,iap.r_type,iap.object,iap.permissions,iap.tid,iap.person)
VALUES (?,?,?,?,?,?,?,?,?,?`);
`
</code></pre>

This are the both query I want oracle query to work same as postgres and delete not update query when matched, I tries using delete statement but its not working. Oracle document stated that used delete where clause to clean up data but not working in my case. I am unable to understand what I am doing wrong.

Delete not working while doing insert and update using merge. Thank you in advance.

Query which I tried:

<pre><code>
 `MERGE INTO IAG_PERMISSION IAP 
  USING dual d
  ON (iap.id='AccessDataStaging' AND iap.R_TYPE ='App1' AND iap.OBJECT ='Obj' AND iap.S_type ='User'
  AND iap.PERSON ='Application New')
  WHEN NOT MATCHED THEN    INSERT(iap.id,iap.bid,iap.aid,iap.s_type,iap.subject,iap.r_type,iap.object,iap.permissions,iap.tid,iap.person)
  VALUES ('of2167aa-769e-4621-b7ff-61b2f44741ea',64,  'AccessDataStaging','User','Sakshi','App1','Obj','N',64,'Application New')
  WHEN MATCHED THEN UPDATE SET iap. Subject = 'Sakshi', iap. PERMISSIONS ='K'
  DELETE WHERE iap.PERSON='Application New' AND iap. Permissions != 'K';
`
</code></pre>

Upvotes: 1

Views: 521

Answers (1)

Littlefoot
Littlefoot

Reputation: 143053

"Delete not working" means, I presume, that no rows were deleted.

At first sight, code you posted looks OK. Can't test it, we don't have your tables nor data.

However, did you carefully read what documentation says?

Specify the DELETE where_clause to clean up data in a table while populating or updating it. The only rows affected by this clause are those rows in the destination table that are updated by the merge operation. The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion.

This is the ON clause:

ON (iap.id='AccessDataStaging' AND 
    iap.R_TYPE ='App1' AND 
    iap.OBJECT ='Obj' AND 
    iap.S_type ='User' AND 
    iap.PERSON ='Application New')              --> [A]

This is the DELETE WHERE clause:

DELETE WHERE iap.PERSON='Application New' AND   --> [A]
             iap. Permissions != 'K'            --> [B]

[A] conditions match in both clauses, but - there's no match for [B] condition in the ON clause, so I guess that this is the reason why rows weren't deleted.

Upvotes: 0

Related Questions