如何删除重复次数大于1的数据？附重复数据查询SQL脚本

Got it, let's figure out how to remove those duplicate entries you identified in the PatientDemographics2 table. First, let's recap your existing query that finds the duplicates:

SELECT LastName, FirstName, DateOfBirth, Count(*) As Duplicates 
FROM PatientDemographics2 
GROUP BY FirstName, LastName, DateOfBirth 
HAVING count(*) >1 
ORDER BY LastName, FirstName Asc;

Great—you already know which groups have duplicates. Now here are a few reliable methods to delete them, depending on your database system and table setup:

Method 1: Using Window Functions (Modern Databases: SQL Server, PostgreSQL, MySQL 8.0+, Oracle) #

This is my go-to because it's clear and lets you control exactly which record to keep (e.g., the oldest, newest, or any specific one). We'll use ROW_NUMBER() to assign a number to each record in a duplicate group, then delete all records where the number is greater than 1.

WITH DuplicateRecords AS (
    SELECT 
        *,
        -- Partition by your duplicate-check fields, order by a unique column to pick which record to keep
        ROW_NUMBER() OVER (
            PARTITION BY LastName, FirstName, DateOfBirth 
            ORDER BY PatientID ASC -- Replace with your primary key; ASC keeps the oldest, DESC keeps the newest
        ) AS RowNum
    FROM PatientDemographics2
)
DELETE FROM DuplicateRecords
WHERE RowNum > 1;

Note: #

If you don't have a primary key (like PatientID), you can use ORDER BY (SELECT NULL) to pick a random record to keep—but it's better to have a unique identifier to ensure consistency.

Method 2: Self-Join (Works for Almost All Databases, Including Older MySQL) #

If you're using an older database that doesn't support window functions, a self-join is a solid alternative. You'll need a unique column (like a primary key) to avoid deleting all records in a duplicate group.

DELETE p1
FROM PatientDemographics2 p1
INNER JOIN PatientDemographics2 p2 
    ON p1.LastName = p2.LastName 
    AND p1.FirstName = p2.FirstName 
    AND p1.DateOfBirth = p2.DateOfBirth
    AND p1.PatientID > p2.PatientID; -- Delete records with higher IDs, keep the lower (older) one

Method 3: Temp Table for Tables Without Unique IDs #

If your table doesn't have any unique identifier at all, this method is safer to avoid accidentally deleting all records:

1. First, create a temp table to store only unique records:

CREATE TABLE TempUniquePatients AS
SELECT DISTINCT LastName, FirstName, DateOfBirth -- Add all other columns you need to preserve
FROM PatientDemographics2;

2. Backup your original table first! Then clear it:

TRUNCATE TABLE PatientDemographics2;

3. Insert the unique records back into the original table:

INSERT INTO PatientDemographics2 (LastName, FirstName, DateOfBirth) -- List all columns here
SELECT LastName, FirstName, DateOfBirth FROM TempUniquePatients;

4. Clean up the temp table:

DROP TABLE TempUniquePatients;

Critical Precaution: #

Before running any delete query, always test it first with a SELECT to make sure you're targeting the right records. For example, replace DELETE with SELECT * in the CTE or self-join query to preview which rows will be removed.

内容的提问来源于stack exchange，提问作者John Molina