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
– Gordon Linoff, Commented Aug 12, 2014 at 13:40
Duplicate of stackoverflow.com/questions/4685173/… Ans: stackoverflow.com/questions/4685173/… — Surya
– Surya, Commented Aug 12, 2014 at 13:48
@user3877230 Is 'ID' meant to be used for a single column PK? — Solkar
– Solkar, Commented Aug 12, 2014 at 14:07

Gordon Linoff · Accepted Answer · 2014-08-12 13:40:19Z

11

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);

answered Aug 12, 2014 at 13:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

giusti · Accepted Answer · 2018-08-18 20:45:34Z

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, 2018 at 20:45

giusti

3,5483 gold badges31 silver badges48 bronze badges

answered Aug 12, 2014 at 14:31

Karan Jawalkar

212 bronze badges

Collectives™ on Stack Overflow

Delete duplicate columns in SQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related