CODEWITHSUNDEEP
sqlpostgresql
avi
avi

Reputation: 1886

How to delete a record by its OID?

I have a weird case which I have no idea how it happened.

This is my table:

id
date
amount

where id can not be NULL and is auto increasing.

Someone last year the system created the following situation:

OID        id     date    amount
710604512 197 2015-03-11 10657.61
710604513 197 2015-03-11 10657.61

This causes huge problems as id should be unique.
I can't fix this from regular SQL because any action I'll do will be done on both rows.

One of them needs to be deleted.

The solution of deleting both and inserting one is unacceptable as I can not play with the dates (it records the date of creation and the logs will show it)

How can I delete the row by its OID?

Upvotes: 0

Views: 712

Answers (3)

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23098

If id, date, amount are the business key in your case, you can remove all records beyond the second by grouping by these columns. Something like this:

DELETE FROM theTable
WHERE OID IN (
    SELECT OID 
    FROM (  SELECT ROW_NUMBER() OVER (PARTITION BY id, date, amount) AS RowNo, OID 
            FROM tab) x 
    WHERE x.RowNo > 1);

Note: this should work regardless of the number of duplicates.

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39527

Assuming you have want to keep the row with max OID for each ID, you can use this:

delete 
from your_table t1
    using (
        select id, max(OID)
        from your_table
        group by id
        ) t2
where t1.id = t2.id and t1.OID <> t2.OID;

Or:

delete
from your_table t1
where exists (
    select 1
    from your_table t2
    where t1.id = t2.id
    and t1.OID < t2.OID
);

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522719

If you want to delete the record with the smaller OID should duplicates occur, then you can try this:

WITH cte AS (
   SELECT *,
          ROW_NUMBER() OVER (PARTITION BY id, date, amount ORDER BY OID DESC) AS rn
   FROM yourTable
) 
DELETE FROM cte WHERE rn=2;     -- or rn >=2 to delete all duplicates

To delete the record with the greater OID, just change the ORDER BY clause to this:

ORDER BY OID

Upvotes: 2

Related Questions