DELETE multiple tables based on a query result

Admin 1
Viewed 121

Our database didn't implement ON DELETE CASCADE so I need to create a script or a stored procedure to delete records in multiple tables given the results of Ids in a QUery:

I tried it in stored procedure but I cannot assign multiple rows in a variable:

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
sql-delete left-join join mysql
1 Answers

You can do it with a multi-table DELETE statement:

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 = ?;

Change ? with the Status value that you want.

See a simplified demo.

user_104988280
Related