How to delete rows in SQL that contain duplicate values in just one column?

Question

I have the following data in a table:

id	name	symbol
1	Two	Three
2	Two	Three
3	Three	Three
4	Three	Three
5	Three	Three

and want to delete all rows such as the column name only contains unique values. It doesn't matter which rows gets deleted in case of duplicate values in column name.

So desired output would be e.g.

id	name	symbol
1	Two	Three
3	Three	Three

I have a postgres td and this is what I tried based on a tutorial:

;with cte as
(
    select
        *,
        row_num = row_number() over (partition by name order by ID)
    from public.tentacle_ticker
)
delete
from cte
where row_num > 1;

which returns column "row_num" does not exist

`row_num = ` is a syntax extension of SQL Server. In PostgreSQL you can use the SQL Standard form ` as row_num`. — The Impaler, Aug 19 '22 at 15:34

score 1 · Accepted Answer · answered Aug 19 '22 at 15:38

1

Not sure if this is what you require. An expanded comment more than an answer

   delete from table
    where
    id not in
    (
    select min(id)
    from
    table 
    group by name,symbol
    )

answered Aug 19 '22 at 15:38

Nathan_Sav

8,466
2
13
20

How to delete rows in SQL that contain duplicate values in just one column?

1 Answers1