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!

Answer 1

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