Delete Duplicate Data in SQL: Step-by-Step

1 year ago
Isabella Edwards
1 minute

To remove duplicate data, you can use the DELETE statement and CTE (Common Table Expressions) in SQL. Here is an example:

Firstly, using CTE to mark duplicate data rows as duplicates and retaining one of them.

WITH CTE AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY (SELECT NULL)) AS rn
  FROM your_table
)
DELETE FROM CTE
WHERE rn > 1;

In this example, assume your_table is a table containing duplicate data, with column1 and column2 being used to determine if the data is duplicate. The ROW_NUMBER() function assigns a row number to each data row within a group, with a row number greater than 1 indicating that the data row is a duplicate. Lastly, the DELETE statement is used to remove duplicate data rows.

Please make sure to backup your data before carrying out the deletion operation to avoid accidentally deleting important data.

bannerAds