Deleting duplicate value at the same column in table of sql

#sql

Sometime, the table has duplicate value as string, number or etc,... And we want to clean that data. But the data is dynamic so we can not hard code for all case of table.

fruits table

id	name
1	Orange 🍊
2	Apple 🍎
3	Orange 🍊
4	Pineapple🍍

In this case, we got have a simple sql like that:

DELETE
  FROM fruits a
 USING fruits b
 WHERE a.id > b.id
   AND a.name = b.name;

Result after excute query

id	name
1	Orange 🍊
2	Apple 🍎
4	Pineapple🍍

If several columns have the same names but the datatypes do not match, the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an EQUIJOIN.

Find out USING in this article

Enjoy your time 🪴
Thanks for reading.

Top comments (1)

Rodrigo Kammer • Aug 31 '21

Here's another approach:

WITH Dups AS(
   SELECt [name],
          ROW_NUMBER() OVER(PARTITION BY [name]
                                ORDER BY [Id]) AS RN
      FROM dbo.fruits
)
DELETE FROM Dups WHERE RN > 1

DEV Community

Deleting duplicate value at the same column in table of sql

Top comments (1)

Read next

Oracle SQL - Cardinality & Cost

PostgreSQL Semi Join, Unique-ify RHS, Materialized CTEs, and Rows Estimation

Improving Performance of PostgreSQL queries

Procedure overloading in PL/SQL