如何安全删除表中多列多行重复引用？基于ID级联删除父子关系数据

Hey there! Let's break down your two SQL challenges with practical, safe solutions—no guesswork involved.

First, let's clarify: when you say "duplicate reference across multiple columns/rows," I assume you mean records where a combination of columns (not just a single column) are identical, and these records might have dependencies on other tables. The key here is to identify duplicates first, confirm which to keep, then delete safely.

Step 1: Identify duplicate records #

Start by pinpointing exactly which combinations are duplicated. Replace col1, col2, col3 with your actual columns that define a duplicate:

SELECT col1, col2, col3, COUNT(*) AS duplicate_count
FROM your_table
GROUP BY col1, col2, col3
HAVING COUNT(*) > 1;

This shows you all duplicate groups and how many times each repeats.

Step 2: Mark records to delete #

Next, decide which records to keep (usually the earliest/latest one by a unique identifier like id). Use ROW_NUMBER() to tag duplicates—rows with row_num > 1 are the ones to remove:

WITH duplicate_records AS (
    SELECT 
        id, col1, col2, col3,
        ROW_NUMBER() OVER (PARTITION BY col1, col2, col3 ORDER BY id ASC) AS row_num
    FROM your_table
)
SELECT * FROM duplicate_records WHERE row_num > 1; -- Verify these are the records you want to delete!

Step 3: Safe deletion #

Once you’ve confirmed the target records, delete them. If your table has foreign key constraints pointing to it, delete dependent records first or temporarily disable constraints (only if you’re 100% sure it’s safe):

WITH duplicate_records AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (PARTITION BY col1, col2, col3 ORDER BY id ASC) AS row_num
    FROM your_table
)
DELETE FROM your_table
WHERE id IN (SELECT id FROM duplicate_records WHERE row_num > 1);

Critical reminder: Always back up your data before deleting, and run the SELECT version first to confirm you’re not removing unintended records.

Your existing recursive CTE is a great starting point—we just need to expand it to capture all nested child nodes, then delete them in the right order (bottom-up, so you don’t hit foreign key errors).

Assuming your table has these fields: id (primary key), Name, ParentId (links to parent id, root nodes have NULL or 0).

Step 1: Recursively find all related nodes #

First, build a CTE that grabs the target node ("Google") and all its descendants (direct and indirect children):

WITH recursive_nodes AS (
    -- Anchor: Start with the target node
    SELECT 
        id, Name, ParentId,
        1 AS node_level -- Track depth (target is level 1)
    FROM your_table
    WHERE Name = 'Google'
    
    UNION ALL
    
    -- Recursive: Pull all child nodes, incrementing depth
    SELECT 
        t.id, t.Name, t.ParentId,
        rn.node_level + 1 AS node_level
    FROM your_table t
    INNER JOIN recursive_nodes rn ON t.ParentId = rn.id
)
SELECT * FROM recursive_nodes; -- Check that this includes Google, HP, Intel, and HP’s sub-items

Step 2: Delete in the correct order #

To avoid foreign key violations, delete the deepest child nodes first (highest node_level), then work your way up to the parent:

WITH recursive_nodes AS (
    SELECT 
        id, Name, ParentId,
        1 AS node_level
    FROM your_table
    WHERE Name = 'Google'
    
    UNION ALL
    
    SELECT 
        t.id, t.Name, t.ParentId,
        rn.node_level + 1 AS node_level
    FROM your_table t
    INNER JOIN recursive_nodes rn ON t.ParentId = rn.id
)
DELETE FROM your_table
WHERE id IN (
    SELECT id 
    FROM recursive_nodes 
    ORDER BY node_level DESC -- Delete deepest nodes first
);

If your foreign keys are set up with ON DELETE CASCADE, you could simply delete the parent node directly—but using the recursive CTE is safer because it lets you explicitly see all records that will be deleted before you run the delete command.

Pro tip: For large tables, consider deleting in batches to avoid locking the table for too long.

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