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

Question

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>

Littlefoot · Accepted Answer · 2022-11-02 08:03:59Z

0

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

answered Nov 2, 2022 at 8:03

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sakshi Over a year ago

I can't add [B] condition in ON clause. Reasons: Columns referenced in the ON Clause cannot be updated . I need delete to work with update. As per my requirements, (example) if one person has 5 permissions from the beginning and later on if I updated 4 permission then I want the remaining 1 permission to be deleted from database.

Littlefoot Over a year ago

Then consider deleting rows in a separate transaction.

Sakshi Over a year ago

I tried that but when I am doing that insert query is not working and also permissions can be multiple. Query: queryToDelete="DELETE FROM IAG_PERMISSION WHERE person=? AND PERMISSIONS !=? "; and used two jdbc statment: jdbcTemplate.batchUpdate(queryForPermission, parameters1, paramTypes1); jdbcTemplate.batchUpdate(queryToDelete,parameters2,paramTypes2);

Littlefoot Over a year ago

Sorry, I don't know anything about JDBC.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related