How to Delete Duplicate Rows in SQL?

Introduction

SQL databases often suffer from duplicate records, hindering data analysis and operational efficiency. This guide provides practical techniques for removing these redundant entries, whether you're working with customer data, transaction logs, or other datasets. We'll cover SQL syntax, real-world examples, and best practices to ensure data integrity throughout the de-duplication process.

Key Areas Covered

This guide addresses the root causes of duplicate records in SQL, explores various detection and removal methods, details relevant SQL syntax, and highlights best practices for maintaining data quality.

Table of contents

• Removing Duplicate Rows in SQL
  • Identifying Duplicate Entries
  • ROW_NUMBER() for Duplicate Removal
  • Self-Join Method for Duplicate Elimination
  • Creating a New Table with Unique Records
• Frequently Asked Questions

How to Delete Duplicate Rows in SQL?

Several methods exist for eliminating duplicate rows in SQL, each with its own strengths. The optimal approach depends on your database system and specific requirements.

Common Causes of Duplicate Data

Duplicate rows often stem from:

• Data Entry Errors: Human error during manual input.
• Dataset Mergers: Combining datasets without proper de-duplication.
• Faulty Import Processes: Errors during data import procedures.

Identifying Duplicate Records

Before deletion, you must first identify duplicate rows. These typically share identical values across one or more columns.

SQL Syntax:

SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;

Example:

Consider an employees table:

id	name	email
1	Alice	alice@example.com
2	Bob	bob@example.com
3	Carol	carol@example.com
4	Alice	alice@example.com
5	Dave	dave@example.com

To find duplicate emails:

SELECT email, COUNT(*)
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

Output:

email	COUNT(*)
alice@example.com	2

This reveals emails appearing more than once.

Removing Duplicates Using ROW_NUMBER()

The ROW_NUMBER() window function assigns a unique sequential number to each row within a partition, enabling efficient duplicate removal.

SQL Syntax:

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

Example:

To remove duplicate employees based on email:

WITH CTE AS (
    SELECT id, name, email, 
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM employees
)
DELETE FROM CTE
WHERE rn > 1;

Output:

The table will now contain only unique entries.

Removing Duplicates Using a Self Join

A self-join provides another effective method for identifying and deleting duplicates.

SQL Syntax:

DELETE t1
FROM table_name t1
JOIN table_name t2
ON t1.column1 = t2.column1
AND t1.column2 = t2.column2
AND t1.id > t2.id;

Example:

To remove duplicates from employees:

DELETE e1
FROM employees e1
JOIN employees e2
ON e1.email = e2.email
AND e1.id > e2.id;

Output:

The table will now contain only unique entries.

Creating a New Table with Unique Records

Creating a new table containing only unique records and then replacing the original table is a robust and safe approach.

SQL Syntax:

CREATE TABLE new_table AS
SELECT DISTINCT *
FROM old_table;

DROP TABLE old_table;

ALTER TABLE new_table RENAME TO old_table;

Example:

Cleaning up duplicates in employees:

CREATE TABLE employees_unique AS
SELECT DISTINCT *
FROM employees;

DROP TABLE employees;

ALTER TABLE employees_unique RENAME TO employees;

Output:

The employees table now contains only unique rows.

Best Practices for Preventing Duplicates

• Data Validation: Implement validation rules before data insertion.
• Unique Constraints: Use unique constraints on relevant columns.
• Regular Audits: Conduct periodic data checks to maintain accuracy.

Conclusion

Effective duplicate row management is essential for database maintenance. The methods described—ROW_NUMBER(), self-joins, and creating new tables—offer various ways to achieve this. Remember to back up your data before performing any deletion operations.

Frequently Asked Questions

Q1. What causes duplicate rows in SQL databases? A. Data entry errors, import issues, and improper dataset merging.

Q2. How to avoid accidental data loss during de-duplication? A. Back up your data and carefully review your SQL queries.

Q3. Can duplicates be removed without altering the original table? A. Yes, by creating a new table with unique records.

Q4. ROW_NUMBER() vs. DISTINCT for duplicate removal? A. ROW_NUMBER() offers more granular control over which rows are kept. DISTINCT simply removes duplicates.

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