Remove Duplicate Data in SQL Server: Complete Guide

1 year ago
Jackson Davis
1 minute

In SQL Server, you can use the DISTINCT keyword to remove duplicate data. For example, if you have a table named table1 with a column named column1, and you want to query the unique values of column1, you can use the following SQL query statement:

SELECT DISTINCT column1
FROM table1;

This will return unique values of column1. If you want to delete duplicate data in the table, you can use the ROW_NUMBER() function and CTE (Common Table Expression) to achieve this. Here is an example:

WITH CTE AS (
  SELECT column1,
         ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY (SELECT 0)) AS rn
  FROM table1
)
DELETE FROM CTE
WHERE rn > 1;

This query will delete duplicate data in the table, while keeping the first record in each group of duplicates. Please note to make sure you back up the data before executing the deletion operation in case any important data is accidentally deleted.

bannerAds