Delete duplicate columns in SQL

Question

Let's say I have three columns in my SQL database like that

ID | NAME | PHONE
-----------------    
 1 | JEFF | 467
 2 | JEFF | 489
 3 | JOHN | 234
 4 | JACK | 323
 5 | JEFF | 378

I want to write a SQL query that deletes all the rows where every double NAME occurence is detected. This means after running the SQL query, the table should look like this:

ID | NAME | PHONE
-----------------
 1 | JEFF | 467
 2 | JOHN | 234
 3 | JACK | 323

Thank you very much in advance!

Thank you very much, i changed it to this now

delete from product_list y
    where exists (select 1 from product_list y2 where y.model = y2.model and y2.linkid < y.linkid);

but i always get this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'delete * from product_list y where exists (select 1 from product_list y2 whe' at line 3

Thanks in advance!

What database are you using? You should tag your questions appropriately. — Gordon Linoff, Aug 12 '14 at 13:40
Duplicate of http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql Ans: http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql?answertab=active#tab-top — Surya, Aug 12 '14 at 13:48
@user3877230 Is 'ID' meant to be used for a single column PK? — Solkar, Aug 12 '14 at 14:07

score 11 · Answer 1 · answered Aug 12 '14 at 13:40

The standard SQL approach to this is:

delete from yourtable y
    where exists (select 1 from yourtable y2 where y.name = y2.name and y2.id < y.id);

That is, delete all records where there exists a record with the same name and a lower id.

If you only want to return the rows, use the same idea:

select y.*
from yourtable y
where not exists (select 1 from yourtable y2 where y.name = y2.name and y2.id < y.id);

score 0 · Answer 2 · edited Aug 18 '18 at 20:45

0

I think this query should work. In this query Employee is my table name and Employee-id is key.

DELETE from Employee WHERE EmpID in(select EmpID from Employee GROUP BY EmpId HAVING COUNT(*) >1)

edited Aug 18 '18 at 20:45

giusti

3,156
3
29
44

answered Aug 12 '14 at 14:31

Karan Jawalkar

21
2

Delete duplicate columns in SQL

2 Answers2