How to Delete Duplicate Rows in a Table Without a Unique ID?

Remove duplicate rows without unique ID

How can we remove duplicate rows if there are no unique row identifiers in the table? Let's say we have a table with the following data:

<code>| col1 | col2 | col3 | col4 | col5 | col6 | col7 |
|---|---|---|---|---|---|---|
| john | 1 | 1 | 1 | 1 | 1 | 1 |
| john | 1 | 1 | 1 | 1 | 1 | 1 |
| sally | 2 | 2 | 2 | 2 | 2 | 2 |
| sally | 2 | 2 | 2 | 2 | 2 | 2 |</code>

Our goal is to eliminate duplicate rows, leaving:

<code>| col1 | col2 | col3 | col4 | col5 | col6 | col7 |
|---|---|---|---|---|---|---|
| john | 1 | 1 | 1 | 1 | 1 | 1 |
| sally | 2 | 2 | 2 | 2 | 2 | 2 |</code>

A reliable way to avoid the problem of missing unique IDs is to use common table expressions (CTE) and the ROW_NUMBER() function. This combination allows us to identify which rows should be deleted:

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

Let’s break this query down:

• The CTE named "CTE" uses the ROW_NUMBER() function to calculate the row number based on each distinct value in the "col1" column while partitioning by that column.
The
• DELETE statement then deletes rows with a row number (RN) greater than 1, thereby removing any duplicate rows.

Note: The results you provide in your original post may vary due to possible typos. The above output represents the expected result.

The flexibility of this technique allows for the inclusion of multiple columns in the PARTITION BY clause, enabling detection of duplicates based on multiple combinations of criteria.

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