根据查询结果删除多个表

Question

我们的数据库没有实现ON DELETE CASCADE，所以我需要创建一个脚本或存储过程来删除多个表中的记录，给定查询中的id结果:

我尝试在存储过程中，但我不能在一个变量中分配多行:

CREATE PROCEDURE `Delete_card`(IN _status INT)
BEGIN
SET @IDs = SELECT ID FROM TABLE_ID WHERE Status=_status;

DELETE FROM TABLE_A WHERE ID IN (@IDs);
DELETE FROM TABLE_B WHERE ID IN (@IDs);
DELETE FROM TABLE_C WHERE ID IN (@IDs);
DELETE FROM TABLE_D WHERE ID IN (@IDs);
DELETE FROM TABLE_E WHERE ID IN (@IDs);
END

Answer 1

您可以使用多表DELETE语句:

DELETE a, b, c
FROM TABLE_ID t
LEFT JOIN TABLE_A a ON a.id = t.id
LEFT JOIN TABLE_B b ON b.id = t.id
LEFT JOIN TABLE_C c ON c.id = t.id
..................................
WHERE t.Status = ?;

将?更改为您想要的Status值。