How to delete only a single row from 2 duplicate rows?

Question

I have 2 duplicate rows in the table,I want to delete only 1 from that and keep the other row.how can I do that?

You can start by showing us sample data and the logic you want for retaining one row over the other. — Tim Biegeleisen, May 31 '18 at 16:06
If they're exact duplicates, then you'll need to limit how many rows get deleted. As far as I know, postgresql doesn't support `limit` with a `delete`. Take a look at the answers to this question to get a few ideas: [How do I delete a fixed number of rows with sorting in PostgreSQL?](https://stackoverflow.com/questions/5170546/how-do-i-delete-a-fixed-number-of-rows-with-sorting-in-postgresql) — RToyo, May 31 '18 at 16:10
Are you using MySQL or Postgres? They're not the same thing. — Tim Biegeleisen, May 31 '18 at 16:15
@TimBiegeleisen Currently I am working on postgresql ,but this question was asked in an interview.I thought the logic will be same.so i tagged both MySQL and postgresql — user9876969, May 31 '18 at 16:18

score 0 · Answer 1 · answered May 31 '18 at 16:16

The PostGres code might be a little different, but here's an example from TSQL that does it with a CTE:

; WITH duplicates
AS (
    SELECT ServerName ,
           ProcessName ,
           DateCreated ,
           RowRank = ROW_NUMBER() OVER(PARTITION BY ServerName, ProcessName, DateCreated ORDER BY 1)
    FROM dbo.ErrorLog
    )
DELETE e
FROM dbo.ErrorLog e
    JOIN duplicates d
        ON d.ServerName = e.ServerName
        AND d.ProcessName = e.ProcessName
        AND d.DateCreated = e.DateCreated
        AND d.RowRank <> 1

How to delete only a single row from 2 duplicate rows?

1 Answers1