基于表B清理SQLite表A中错误插入的冗余行

核心逻辑 #

表A中有效行的判断规则：同一tournament+year组合下，round为1的记录course必须等于表B的R1值，round=2对应R2，以此类推。基于这个规则，有两种可行方案：

方案1：通过临时表保留有效数据（推荐大数量场景） #

这种方式更高效，尤其适合处理大量数据删除操作：

1. 先验证有效数据：执行以下查询，确认要保留的行是否符合预期

SELECT a.*
FROM A a
JOIN B b ON a.tournament = b.tournament AND a.year = b.year
WHERE 
    (a.round = 1 AND a.course = b.R1)
    OR (a.round = 2 AND a.course = b.R2)
    OR (a.round = 3 AND a.course = b.R3)
    OR (a.round = 4 AND a.course = b.R4);

2. 创建临时表存储有效数据

CREATE TABLE A_valid AS
SELECT a.*
FROM A a
JOIN B b ON a.tournament = b.tournament AND a.year = b.year
WHERE 
    (a.round = 1 AND a.course = b.R1)
    OR (a.round = 2 AND a.course = b.R2)
    OR (a.round = 3 AND a.course = b.R3)
    OR (a.round = 4 AND a.course = b.R4);

3. 替换原表

DROP TABLE A;
ALTER TABLE A_valid RENAME TO A;

方案2：直接删除无效行 #

如果数据量不大，也可以直接执行删除语句：

1. 先验证要删除的行

SELECT a.*
FROM A a
LEFT JOIN B b ON 
    a.tournament = b.tournament 
    AND a.year = b.year
    AND (
        (a.round = 1 AND a.course = b.R1)
        OR (a.round = 2 AND a.course = b.R2)
        OR (a.round = 3 AND a.course = b.R3)
        OR (a.round = 4 AND a.course = b.R4)
    )
WHERE b.tournament IS NULL;

2. 执行删除操作

DELETE FROM A
WHERE NOT EXISTS (
    SELECT 1
    FROM B
    WHERE 
        A.tournament = B.tournament 
        AND A.year = B.year
        AND (
            (A.round = 1 AND A.course = B.R1)
            OR (A.round = 2 AND A.course = B.R2)
            OR (A.round = 3 AND A.course = B.R3)
            OR (A.round = 4 AND A.course = B.R4)
        )
);

注意事项 #

• 操作前务必备份数据：删除或替换表的操作不可逆，建议先导出原表数据再执行。
• 优先验证查询结果：先运行SELECT语句确认有效/无效行是否符合预期，再执行修改操作。
• 大数量场景选方案1：直接删除25000条数据可能会产生较多碎片，临时表替换的方式性能更优。

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