0

I want to delete a quantity of rows in procedure . I tried do it like this ,but I did something wrong.

BEGIN
DECLARE counter INT DEFAULT 0;
SELECT COUNT(*) INTO quantity FROM (SELECT A.idImages FROM images A WHERE A.Posts_idposts = id ) a LIMIT 1;
START TRANSACTION;
WHILE counter< quantity DO 
    SELECT A.idImages INTO imagesid FROM imagesA WHERE A.Posts_idposts = id LIMIT counter,1;
    DELETE FROM images WHERE IdImages = imagesid;
END WHILE;
//other single deletes 
COMMIT;
END

the procedure gets the "id" from the attribute CREATE DEFINER=`root`@`localhost` PROCEDURE `usun_zgloszenie`(id INT)

the procedure lasts indefinitely

mattxml
  • 82
  • 1
  • 8
  • Hi Matt, what error msg you getting? WHere does "id" come from to fit in "WHERE A.Posts_idposts = id"? – zip Nov 30 '19 at 17:44
  • I edited , sorry for inaccuracy – mattxml Nov 30 '19 at 17:48
  • Neither `counter` nor `quantity` change their values in the loops body, so if `counter < quantity` is true at the start of the loop, it will be true forever thus making it an infinite loop. – sticky bit Nov 30 '19 at 18:01
  • SET counter= counter+ 1; doesnt work – mattxml Nov 30 '19 at 18:01
  • Besides that, it looks like you just want to delete all rows in `images` where `posts_idposts` is equal to `id`? In that case there's no need for any loop, that can be done in a single and simple statement: `DELETE FROM images WHERE posts_idpost = id`. A loop very likely performs worse than just a statement. – sticky bit Nov 30 '19 at 18:10
  • I'm soo stupid , i don't know why i didn't do this :( . Thank you very much . – mattxml Nov 30 '19 at 18:23

0 Answers0