CODEWITHSUNDEEP
sqloracle-databaseduplicatessql-delete
Symonds
Symonds

Reputation: 194

Delete duplicate data and keep the latest record using Oracle SQL not working

I have VK_MODIFY table where i want to delete all the duplicate data by combination of ORDERID and EXTORID and keep only the one which has latest SYSTEM_INSERTED_AT.

[![enter image description here][1]][1]

I have tried below query but its not working correctly:

DELETE FROM VK_MODIFY a
WHERE rowid not in
(SELECT min(rowid)
FROM VK_MODIFY b
where a.ORDERID = b.ORDERID and a.EXTORID = b.EXTORID);

Upvotes: 0

Views: 2771

Answers (2)

D.J.
D.J.

Reputation: 386

Here is another example which can solve your problem.

 DELETE FROM VK_MODIFY WHERE ROWID IN(SELECT TEMP.ROWID FROM(
SELECT ROWID,ORDERID, EXTORID, SYSTEM_INSERTED_AT,
ROW_NUMBER()OVER (PARTITION BY ORDERID,EXTORID ORDER BY SYSTEM_INSERTED_AT DESC) RN  FROM VK_MODIFY
)TEMP
WHERE TEMP.RN <> 1)

Best practice is you should have a primary key field in your table, if you have you can use this field instead of rowid.

Upvotes: 3

gsalem
gsalem

Reputation: 2028

Try this:

DELETE FROM VK_MODIFY a
WHERE SYSTEM_INSERTED_AT not in
(SELECT max(SYSTEM_INSERTED_AT)
FROM VK_MODIFY b
where a.ORDERID = b.ORDERID and a.EXTORID = b.EXTORID);

Depending on your schema (indexes, ....), there may be faster solutions.

Upvotes: 2

Related Questions