Finding duplicates and delete them in MYSQL Server

Question

I have many duplicates of name and i need to find and delete them. I want to save only highest value of usedpoints and delete duplicates with lower value of usedpoints.

MYSQL Table Example:

==============================
| name | points | usedpoints |
|----------------------------|
| john | 840    | 1200       |
| john | 230    | 900        |
| jane | 550    | 400        |
| jane | 130    | 245        |
| nick | 130    | 123        |
| nick | 90     | 200        |
==============================

Are you doing this in Sql Server or mysql? The tags don't match the title. — Becuzz, Apr 10 '17 at 13:01

score 0 · Answer 1 · answered Apr 10 '17 at 12:50

0

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

you can use any other filed instead of id.

answered Apr 10 '17 at 12:50

Jenish

535
4
16

I don't understand.. DELETE name FROM myTable – Razvan Apr 10 '17 at 13:29
check this : http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql – Jenish Apr 10 '17 at 13:42

score 0 · Answer 2 · answered Apr 10 '17 at 12:53

You can use Row_Number() to remove dupes

;with cte as (
    Select *
          ,RN = Row_Number() over (Partition By Name Order by usedpoints Desc)
     From  YourTable
)
--Delete from cte where RN>1
Select * from cte where RN>1   -- << Remove is satisfied

score 0 · Answer 3 · answered Apr 10 '17 at 13:41

use your_schema;

drop table if exists temp_names_values; -- names with max usedpoints will be stored in this table
create temporary table temp_names_values as (select name,max(usedpoints ) as mx from test group by name);

drop table if exists temp_max_ids ; -- record ids of names with max usedpoints will be stored in this table
create temporary table temp_max_ids  as (select test.idtest from test,temp_names_values where (test.name = temp_names_values.name and test.usedpoints = temp_names_values.mx));

select * from test where test.idtest not in (select idtest from temp_max_ids )

I considered that table name is test. the last select statement is actually where you should write your delete statement.

score 0 · Answer 4 · answered Apr 10 '17 at 14:19

0

select o.name, oc.dupeCount, o.usedpoints from MyTable o inner join ( SELECT name, COUNT(*) AS dupeCount FROM MyTable GROUP BY name HAVING COUNT(*) > 1 ) oc on o.name = oc.name

I have this to find duplicates, but now i need to delete them ..

answered Apr 10 '17 at 14:19

Razvan

61
2
9

Finding duplicates and delete them in MYSQL Server

4 Answers4