How to Delete Duplicate Rows in SQL Server Without a Unique Key?

Efficiently Removing Duplicate Rows in SQL Server: A CTE Approach

When dealing with SQL Server tables lacking a unique key, eliminating duplicate rows requires a strategic approach. This can be effectively accomplished using common table expressions (CTEs) in conjunction with the ROW_NUMBER() function.

Here's a solution demonstrating this technique:

<code class="language-sql">WITH RowNumberedRows AS (
   SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
       RN = ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1)
   FROM dbo.Table1
)
DELETE FROM RowNumberedRows
WHERE RN > 1;</code>

Detailed Explanation:

The CTE, aptly named RowNumberedRows, assigns a unique row number (RN) to each row within the table. The ROW_NUMBER() function partitions the rows based on the col1 column, assigning sequential numbers within each partition. This ensures that the first occurrence of each duplicate group receives an RN of 1.

The DELETE statement then targets the CTE, removing all rows where RN is greater than 1. This leaves only the first row of each duplicate set, effectively removing the duplicates.

Example:

Applying this query to sample data yields the following outcome:

COL1	COL2	COL3	COL4	COL5	COL6	COL7
john	1	1	1	1	1	1
sally	2	2	2	2	2	2

Observe that the duplicate "john" row has been successfully eliminated.

This method's adaptability is noteworthy. By adjusting the partitioning columns within the ROW_NUMBER() function (e.g., PARTITION BY col1, col2), you can define uniqueness based on multiple columns as needed.

The above is the detailed content of How to Delete Duplicate Rows in SQL Server Without a Unique Key?. For more information, please follow other related articles on the PHP Chinese website!