SHARE
TWEET

Untitled

a guest Apr 26th, 2019 57 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE DEFINER=`Leo_8ILofB`@`%` PROCEDURE `nick_unique_check`()
  2. BEGIN
  3.  
  4.     DROP TEMPORARY TABLE IF EXISTS `names_temp`;
  5.     DROP TEMPORARY TABLE IF EXISTS `rows_temp`;
  6.  
  7.     CREATE TEMPORARY TABLE `names_temp`(
  8.     `name` VARCHAR(50)
  9.     ) ENGINE=memory;
  10.  
  11.     CREATE TEMPORARY TABLE `rows_temp`(
  12.     `guid` INT(11),
  13.     `name` VARCHAR(50)
  14.     ) ENGINE=memory;
  15.  
  16.     INSERT INTO names_temp (`name`) SELECT `name` FROM user_social GROUP BY `name` HAVING COUNT(*) > 1;
  17.  
  18.     INSERT INTO rows_temp (`guid`, `name`) SELECT guid, `name`
  19.     FROM user_social as social
  20.     WHERE (SELECT COUNT(*) FROM user_social WHERE `name` = social.`name`) > 1;
  21.  
  22.     BEGIN
  23.  
  24.         DECLARE count INTEGER DEFAULT 0;
  25.         DECLARE `name` VARCHAR(50);
  26.         DECLARE doneName INTEGER DEFAULT 0;
  27.         DECLARE nickCursore CURSOR FOR SELECT names_temp.`name` FROM names_temp;
  28.         DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET doneName=1;
  29.  
  30.         OPEN nickCursore;
  31.         REPEAT
  32.         FETCH nickCursore INTO `name`;
  33.         IF NOT doneName THEN
  34.  
  35.             SET count = 0;
  36.    
  37.             BEGIN
  38.  
  39.                 DECLARE guid INT(11);
  40.                 DECLARE doneGuid INTEGER DEFAULT 0;
  41.                 DECLARE guidCursore CURSOR FOR SELECT rows_temp.guid FROM rows_temp WHERE rows_temp.`name` = `name`;
  42.                 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET doneGuid=1;
  43.  
  44.                 OPEN guidCursore;
  45.                 REPEAT
  46.                 FETCH guidCursore INTO guid;
  47.                 IF NOT doneGuid THEN
  48.                 SET count = count + 1;
  49.                 UPDATE user_social SET user_social.`name` = CONCAT(`name`, " (", count, ")") WHERE user_social.guid = guid;
  50.                 END IF;
  51.                 UNTIL doneGuid END REPEAT;
  52.                 CLOSE guidCursore;
  53.  
  54.             END;
  55.  
  56.         END IF;
  57.         UNTIL doneName END REPEAT;
  58.         CLOSE nickCursore;
  59.  
  60.     END;
  61.  
  62.     DROP TEMPORARY TABLE IF EXISTS `names_temp`;
  63.     DROP TEMPORARY TABLE IF EXISTS `rows_temp`;
  64.  
  65. END
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top