Results 1 to 1 of 1
Feb 18, 2010, 11:18 #1
- Join Date
- Dec 2008
- 2 Post(s)
- 0 Thread(s)
Remove duplicates and update referencing tables
I have a table `location` that contains duplicate records I need to remove. There are two other tables `img_locationShown` and `img_locationCreated` that both reference the `location` table. So as well as removing the duplicates from `location` I also need to update these two tables so any rows in these tables referencing a duplicate row in `location` are updated.
I've come up with a procedure to do this, it seems to work okay, but I'm not sure if there's a better way to do this, or any potential problems with the below?
DELIMITER // DROP PROCEDURE IF EXISTS remove_location_dupes// CREATE PROCEDURE remove_location_dupes() BEGIN DECLARE id, Sublocation, City, ProvinceState, country, WorldRegion INT; DECLARE done INT DEFAULT 0; DECLARE cur1 CURSOR FOR SELECT location.id, location.Sublocation, location.City, location.ProvinceState, location.country, location.WorldRegion FROM location; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; # Disable Foreign Key Constraints so we can empty the location table SET foreign_key_checks = 0; # Truncate location table TRUNCATE TABLE location; # Loop through location table the_loop: LOOP FETCH cur1 INTO id, Sublocation, City, ProvinceState, country, WorldRegion; IF done THEN SET foreign_key_checks = 1; CLOSE cur1; LEAVE the_loop; END IF; # Try inserting the location into the location table INSERT IGNORE INTO location SET location.Sublocation = Sublocation, location.City = City, location.ProvinceState = ProvinceState, location.country = country, location.WorldRegion = WorldRegion; IF LAST_INSERT_ID() THEN # Update img_locationShown and img_locationCreated with the correct location id UPDATE img_locationShown SET img_locationShown.location_id = LAST_INSERT_ID() WHERE img_locationShown.location_id = id; UPDATE img_locationCreated SET img_locationCreated.location_id = LAST_INSERT_ID() WHERE img_locationCreated.location_id = id; END IF; END LOOP the_loop; END // DELIMITER ;