如何使用多表LEFT JOIN查询结果批量删除3张表的对应CodeName数据

实现方案 #

方案1：已知待删除CodeName为ABC、DEF，直接执行删除语句 #

适合你已经确认待删除目标值的场景，直接执行三条删除语句即可：

DELETE FROM Tbl_StockData_Daily WHERE CodeName IN ('ABC', 'DEF');
DELETE FROM Tbl_StockData_Weekly WHERE CodeName IN ('ABC', 'DEF');
DELETE FROM Tbl_StockData_Monthly WHERE CodeName IN ('ABC', 'DEF');

方案2：直接嵌套原有查询批量删除，无需手动指定值 #

如果不想手动复制查询结果，或者后续需要定期执行该清理操作，可以直接把你之前的查询不匹配CodeName的语句作为子查询嵌入，自动匹配待删除值：

-- 删除日线表对应数据
DELETE FROM Tbl_StockData_Daily 
WHERE CodeName IN (
    SELECT Qry_StockData_Names.CodeName
    FROM Qry_StockData_Names 
    LEFT JOIN Tbl_StockData_Codes ON Qry_StockData_Names.CodeName = Tbl_StockData_Codes.CodeName
    WHERE Tbl_StockData_Codes.CodeName IS NULL
);

-- 删除周线表对应数据
DELETE FROM Tbl_StockData_Weekly 
WHERE CodeName IN (
    SELECT Qry_StockData_Names.CodeName
    FROM Qry_StockData_Names 
    LEFT JOIN Tbl_StockData_Codes ON Qry_StockData_Names.CodeName = Tbl_StockData_Codes.CodeName
    WHERE Tbl_StockData_Codes.CodeName IS NULL
);

-- 删除月线表对应数据
DELETE FROM Tbl_StockData_Monthly 
WHERE CodeName IN (
    SELECT Qry_StockData_Names.CodeName
    FROM Qry_StockData_Names 
    LEFT JOIN Tbl_StockData_Codes ON Qry_StockData_Names.CodeName = Tbl_StockData_Codes.CodeName
    WHERE Tbl_StockData_Codes.CodeName IS NULL
);

注意事项 #

如果你的数据库支持事务操作，建议先开启事务执行，确认删除行数符合预期后再提交，避免误删数据：

BEGIN TRANSACTION;

-- 执行上面的DELETE语句后，先验证删除结果是否正确
-- 例：验证日线表中已无对应CodeName数据
SELECT COUNT(*) FROM Tbl_StockData_Daily WHERE CodeName IN ('ABC','DEF');

-- 确认没问题再提交
COMMIT;
-- 结果不符合预期就执行回滚
-- ROLLBACK;

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