Skip to content

Instantly share code, notes, and snippets.

@ancho85
Created July 18, 2016 13:40
Show Gist options
  • Save ancho85/d65ab76ff972c51ecd8e9990a8bbef06 to your computer and use it in GitHub Desktop.
Save ancho85/d65ab76ff972c51ecd8e9990a8bbef06 to your computer and use it in GitHub Desktop.
MySQL delete duplicate rows
DELIMITER $$
DROP PROCEDURE IF EXISTS `anchoDeleteDuplicate` $$
CREATE PROCEDURE `anchoDeleteDuplicate`()
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE uid integer;
DECLARE cWebFlotaRow cursor for
select wfr.internalid from WebFlotaRow wfr
inner join WebFlotaRow wfr2
on wfr.masterid = wfr2.masterid
and wfr.rowNr = wfr2.rowNr
and wfr.internalid > wfr2.internalid
group by wfr.internalid;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
open cWebFlotaRow;
cWebFlotaRow_loop: LOOP
fetch cWebFlotaRow into uid;
IF done THEN LEAVE cWebFlotaRow_loop; END IF;
set @deleteText = CONCAT("DELETE FROM WebFlotaRow WHERE internalid = ", uid,';');
PREPARE stmt_name FROM @deleteText;
EXECUTE stmt_name;
DEALLOCATE PREPARE stmt_name;
END LOOP cWebFlotaRow_loop;
CLOSE cWebFlotaRow;
END $$
DELIMITER ;
CALL anchoDeleteDuplicate();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment