Want to iterate over a table row by row, and if there's a matching row in another table, delete with limit 1

Question

I am essentially trying to do manual set subtraction (without getting rid of duplicates, I need to keep duplicates if they exist). I have one table I want to iterate over row by row, and if there's a match in a much larger table, delete a single instance of the match in the larger table, using a stored procedure. How can I go about this? I have three where criteria and have the follow written -

DROP PROCEDURE deleteOverlappingBottles;
DELIMITER ;;

CREATE PROCEDURE deleteOverlappingBottles()
BEGIN
DECLARE n INT default 0;
DECLARE i INT default 0;
SELECT 10 FROM allBottles INTO n;
SET i = 0;
WHILE i<n DO
    #
    What goes here?  How can I get the current row of allBottles and specifically the values needed for the proceeding where clause?
    DELETE FROM historicdata2Delete as t
    WHERE t.unitADcode = ab.bottleBarcode AND t.bottle_timestamp = ab.t_stamp
    AND t.prepackId = ab.parentPrepackId LIMIT 1;
    SET i = i+1;
END WHILE;
End;
;;
DELIMITER ;

CALL deleteOverlappingBottles();

Using MySQL 5.6.

historicDataToDelete
unitADcode            bottle_timestamp        prepackId
barcode1              timestamp1              id1
barcode1              timestamp1              id1
barcode2              timestamp2              id2
barcode3              timestamp3              id3
barcode3              timestamp3              id3

allBottles
bottleBarcode         t_stamp                 parentPrepackId
barcode1              t_stamp1                id1
barcode1              t_stamp1                id1
barcode3              t_stamp3                id3

So going row by row through allBottles and deleting one row per instance would leave historicData2Delete as such

historicDataToDelete
unitADcode            bottle_timestamp        prepackId
barcode2              timestamp2              id2
barcode3              timestamp3              id3

Here is what I have so far but it is not working, any ideas as to why?

DELIMITER ;;

CREATE PROCEDURE deleteOverlappingBottles()
BEGIN
DECLARE n INT default 0;
DECLARE i INT default 0;

SELECT 10 FROM allBottles INTO n;
SET i = 0;
WHILE i<n DO
    SET @barcode = (SELECT bottleBarcode FROM allBottles LIMIT i,1);
    SET @tstamp = (SELECT t_stamp FROM allBottles LIMIT i, 1);
    SET @preIdx = (SELECT parentPrepackId FROM allBottles LIMIT i, 1);
    DELETE FROM historicdata2Delete
    WHERE unitADcode = @barcode AND bottle_timestamp = @tstamp AND prepackIdx = @preIdx LIMIT 1;
    SET i = i+1;
END WHILE;
End;
;;
DELIMITER ;

CALL deleteOverlappingBottles();

It says result consisted of more than one row. It did run for a while before, over an hour then I stopped it but nothing was deleted.

Answer 1

It looks as if you are only missing a cursor.

Reference code can be found here: MySql Cursors

CREATE PROCEDURE deleteOverlappingBottles()
BEGIN
DECLARE done INT DEFAULT FALSE;
-- Variables for FETCH (change data types to your needs)
DECLARE vBottleBarcode CHAR(100);
DECLARE vt_stamp TIMESTAMP;
DECLARE vParentPrepackId INT;
-- cursor to iterate through the table
DECLARE cur1 CURSOR FOR SELECT BottleBarcode, t_stamp, ParentPrepackId FROM allBottles;
-- apparently MySql needs this to handle the end of the table
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur1;

read_loop: LOOP
    -- get the three column values for the current row
    FETCH cur1 INTO vBottleBarcode, vt_stamp, vParentPrepackId;
    IF done THEN
      LEAVE read_loop;
    END IF;
    -- use the variables from FETCH to identify the row in historicdataToDelete
    DELETE FROM historicdata2Delete as t
    WHERE t.unitADcode = vBottleBarcode AND t.bottle_timestamp = vt_stamp
    AND t.prepackId = vParentPrepackId
    LIMIT 1; -- delete only 1 row
  END LOOP;

  CLOSE cur1;
END LOOP;
End;

Disclaimer: I am not familiar with MySql. The syntax might be slightly wrong, please adapt to your needs.