如何删除SELECT JOIN连接查询返回的结果数据

重要提醒：删除操作不可逆，执行前务必备份对应数据，可先将删除语句替换为SELECT查询验证命中结果，确认符合预期后再执行删除

场景1：仅删除TableB中匹配条件的记录 #

这是和原查询逻辑最匹配的场景，原查询返回的就是TableB中符合关联条件和时间范围的TransactionID，使用兼容性最好的IN子查询写法即可，支持绝大多数主流关系型数据库：

DELETE FROM TableB
WHERE TransactionID IN (
    SELECT t2.TransactionID
    FROM TableA AS t1
    INNER JOIN TABLEB AS t2 ON t1.TransactionID = t2.TransactionID
    WHERE t2.accountingDate BETWEEN '2010-01-01 00:00:00.0000000' AND '2011-12-30 00:00:00.0000000'
);

如果使用MySQL数据库，直接执行上述语句可能触发同表子查询报错，可嵌套一层子查询别名规避：

DELETE FROM TableB
WHERE TransactionID IN (
    SELECT temp.TransactionID
    FROM (
        SELECT t2.TransactionID
        FROM TableA AS t1
        INNER JOIN TABLEB AS t2 ON t1.TransactionID = t2.TransactionID
        WHERE t2.accountingDate BETWEEN '2010-01-01 00:00:00.0000000' AND '2011-12-30 00:00:00.0000000'
    ) AS temp
);

场景2：同时删除TableA、TableB中匹配的关联记录 #

不同数据库的多表关联删除语法略有差异：

• MySQL 支持直接在DELETE后指定要删除的表别名，一次删除两表数据：

DELETE t1, t2
FROM TableA AS t1
INNER JOIN TABLEB AS t2 ON t1.TransactionID = t2.TransactionID
WHERE t2.accountingDate BETWEEN '2010-01-01 00:00:00.0000000' AND '2011-12-30 00:00:00.0000000';

• SQL Server、PostgreSQL 不支持一次DELETE语句删多表，需要分两次执行，注意如果有外键约束，先删从表（TableB）数据再删主表（TableA）数据，避免触发约束报错：

-- 先删TableB匹配数据
DELETE FROM TableB
WHERE TransactionID IN (
    SELECT t2.TransactionID
    FROM TableA AS t1
    INNER JOIN TABLEB AS t2 ON t1.TransactionID = t2.TransactionID
    WHERE t2.accountingDate BETWEEN '2010-01-01 00:00:00.0000000' AND '2011-12-30 00:00:00.0000000'
);

-- 再删TableA匹配数据
DELETE FROM TableA
WHERE TransactionID IN (
    SELECT t1.TransactionID
    FROM TableA AS t1
    INNER JOIN TABLEB AS t2 ON t1.TransactionID = t2.TransactionID
    WHERE t2.accountingDate BETWEEN '2010-01-01 00:00:00.0000000' AND '2011-12-30 00:00:00.0000000'
);

注意事项 #

• 执行删除前可将DELETE关键字替换为SELECT *，核对返回的结果行数、内容完全符合预期后再执行删除
• 如果表存在外键约束，严格按照先从表后主表的顺序执行删除，否则会抛出约束冲突错误
• 命中数据量超过1万条时建议分批删除，避免长事务锁表影响正常业务运行

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