How to Remove Duplicate Rows Except One

Delete all Duplicate Rows except for One in MySQL?

Editor warning: This solution is computationally inefficient and may bring down your connection for a large table.

NB - You need to do this first on a test copy of your table!

When I did it, I found that unless I also included AND n1.id <> n2.id, it deleted every row in the table.

1. If you want to keep the row with the lowest id value:

   DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name
   

2. If you want to keep the row with the highest id value:

   DELETE n1 FROM names n1, names n2 WHERE n1.id < n2.id AND n1.name = n2.name
   

I used this method in MySQL 5.1

Not sure about other versions.

---

Update: Since people Googling for removing duplicates end up here
Although the OP's question is about DELETE, please be advised that using INSERT and DISTINCT is much faster. For a database with 8 million rows, the below query took 13 minutes, while using DELETE, it took more than 2 hours and yet didn't complete.

INSERT INTO tempTableName(cellId,attributeId,entityRowId,value)
    SELECT DISTINCT cellId,attributeId,entityRowId,value
    FROM tableName;

Remove duplicate records except the first record in SQL

Use a CTE (I have several of these in production).

;WITH duplicateRemoval as (
    SELECT 
        [name]
        ,ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY [name]) ranked
    from #myTable
    ORDER BY name
)
DELETE
FROM duplicateRemoval
WHERE ranked > 1;

Explanation: The CTE will grab all of your records and apply a row number for each unique entry. Each additional entry will get an incrementing number. Replace the DELETE with a SELECT * in order to see what it does.

How to remove duplicate rows except one?

You can choose to keep the min or max of rowid grouping by the 3 columns shown.

delete from myTable
where rowid not in (select min(rowid)
                    from myTable
                    group by name,company,position)

Eliminating duplicate rows except one column with condition

May be you want this (eliminate the last row if more than one)?

select t.*
from (select t.*
            , row_number() over (partition by policy, plan
                          order by code desc
                         ) AS RN
            , COUNT(*) over (partition by policy, plan) AS RC
      from t
     ) t
where RN > 1 OR RN=RC;

Output:

    row policy  plan    code    RN  RC
1   1   a   aa  111 1   1
2   2   b   bb  112 2   2
3   5   c   cc  112 2   3
4   4   c   cc  111 3   3
5   7   d   dd  999 1   1

Delete duplicate rows except one

Quick and easy via a CTE in concert with the window function row_number()

Example or dbFiddle

;with cte as ( 
    Select *
          ,RN = row_number() over (partition by address order by DateEntered Desc) 
     From YourTable
)
Delete from cte where RN>1

The Updated Table

Address         DateEntered
7 Sterling Rd   2012-07-08
10 Park Ave     2021-05-20

Delete all but one duplicate record

ANSI SQL Solution

Use group by in a subquery:

delete from my_tab where id not in 
(select min(id) from my_tab group by profile_id, visitor_id);

You need some kind of unique identifier(here, I'm using id).

MySQL Solution

As pointed out by @JamesPoulson, this causes a syntax error in MySQL; the correct solution is (as shown in James' answer):

delete from `my_tab` where id not in
( SELECT * FROM 
    (select min(id) from `my_tab` group by profile_id, visitor_id) AS temp_tab
);

How to remove duplicate MySQL records (but only leave one)

You can use this to keep the row with the lowest id value

DELETE e1 FROM contacts e1, contacts e2 WHERE e1.id > e2.id AND e1.email = e2.email;

this an example link link 1

or you can change > to < for keep the highest id

DELETE e1 FROM contacts e1, contacts e2 WHERE e1.id < e2.id AND e1.email = e2.email;

this an example link link 2

---

Related Topics