0

i wrote this procedure to optimize the deletion of releated elements in different table:

DROP PROCEDURE if exists demo;

DELIMITER $$ 
CREATE PROCEDURE demo()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE _id VARCHAR(255) default '';
    DECLARE _cursor CURSOR FOR SELECT id FROM souce_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN _cursor;
    _loop: LOOP
        FETCH _cursor INTO _id;
        IF done THEN LEAVE _loop;
        END IF;
        delete FROM dest_table1 where id = _id;
        delete FROM dest_table2  where id = _id;
        delete FROM dest_table3  where id = _id;
        delete FROM dest_table4  where id = _id;
        delete FROM dest_table5  where id = _id;
        delete from dest_table6  where id = _id;
        delete from dest_table7  where id = _id;
    END LOOP _loop;
    CLOSE _cursor;
END $$ DELIMITER ;

call demo('project_push_id');

i'm looking to parameterize souce_table (dest_table(N) are always the same).

i've tried alredy this, unsuccessfully:

SET @query = CONCAT("SELECT id FROM cacomo.",source_table);
PREPARE flex_query FROM @query;
DECLARE _cursor CURSOR FOR EXECUTE flex_query;
ametta
  • 1
  • 2
  • Do you have to use a cursor? You can do this with a single `DELETE` query that left-joins all the tables. – Barmar Aug 30 '23 at 16:19
  • `DELETE t1, t2, t3, ..., t7 FROM source_table s LEFT JOIN dest_table1 AS t1 ON t1.id = s.id LEFT JOIN dest_table2 AS t2 ON t2.id = s.id ...` – Barmar Aug 30 '23 at 16:21
  • 1
    Doing it as one big query might be slow, but you can do seven separate deletes that join each dest table with the source table. – Barmar Aug 30 '23 at 16:22

0 Answers0