Cleans up duplicate codes from salesrule_coupons table provided times_used is 0

cleanupDuplicateCoupons.sql

-- Dissalow non-deterministic use of columns not named in GROUP BY
-- See: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
SET SESSION sql_mode = CONCAT_WS(",", (SELECT @@session.sql_mode), 'ONLY_FULL_GROUP_BY');

DROP PROCEDURE IF EXISTS deleteDuplicateCoupons;

DELIMITER $$

CREATE PROCEDURE deleteDuplicateCoupons()
BEGIN
  DECLARE lastCount INT;
  DECLARE currCount INT;

  SET lastCount = NULL;
  SET currCount = NULL;

  SELECT current_time;
  SELECT count(*) as total_coupons FROM salesrule_coupon;
  delete_duplicates:  LOOP

    -- count number of codes with duplicate rows
    SET currCount = (SELECT SUM(count) as count FROM (
      SELECT count(*) AS count FROM salesrule_coupon AS c GROUP BY c.code HAVING count > 1
    ) AS counts);
    SELECT lastCount, currCount;

    -- exit loop when count reaches zero OR if delete didn't do anything
    IF currCount IS NULL OR lastCount = currCount THEN
      LEAVE delete_duplicates;
    END IF;
    SET lastCount = currCount;

    -- find duplicates and delete first coupon_id from each resulting grouped row
    DELETE m.* FROM salesrule_coupon AS m INNER JOIN (SELECT coupon_id FROM (
        SELECT COUNT(*) as count,
          SUBSTRING_INDEX(GROUP_CONCAT(coupon_id ORDER BY times_used ASC, coupon_id ASC), ",", 1) AS coupon_id,
          SUBSTRING_INDEX(GROUP_CONCAT(times_used ORDER BY times_used ASC, coupon_id ASC), ",", 1) AS times_used
        FROM salesrule_coupon AS c GROUP BY c.code HAVING count > 1 LIMIT 100000
      ) AS g ORDER BY coupon_id
    ) AS d ON m.coupon_id = d.coupon_id WHERE m.times_used = 0;
  END LOOP;
  SELECT count(*) as total_coupons FROM salesrule_coupon;
  SELECT current_time;
END$$

DELIMITER ;

CALL deleteDuplicateCoupons();