DROP PROCEDURE `max_wid`;
DELIMITER ;;
CREATE PROCEDURE `max_wid` (OUT `y` int)
SQL SECURITY INVOKER
BEGIN
SELECT MAX(wid) into y from works;
END;;
DELIMITER ;DELIMITER ;;
CREATE PROCEDURE `first_dup` (OUT `t` int)
SQL SECURITY INVOKER
BEGIN
SELECT wid into t FROM works GROUP BY wid HAVING ( COUNT(wid) > 1 ) LIMIT 0,1;
END;;
DELIMITER ;The procedure itself - finding first duplicated data - and updating it to maximum possible id
After running this code till ZERO duplicates just create unique Primary key for Your table
CALL `max_wid`(@`y`);
CALL `first_dup`(@`t`);
UPDATE works set wid = ((SELECT @y) +1) WHERE wid like (SELECT @t) LIMIT 1;Trackback URL for this post:
http://itua.name/en/trackback/944
