Created
July 18, 2016 13:40
-
-
Save ancho85/d65ab76ff972c51ecd8e9990a8bbef06 to your computer and use it in GitHub Desktop.
MySQL delete duplicate rows
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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