Update column value based on other column with manage duplicate value MySQL query

Question

I need to update column value based on other column with adding separator and with manage duplicates values using sql query.

For example :

I have one database table A.

Here in it two columns : 1) locality 2) slug

Locality column already have data. So need to update 'slug' column based on locality value. Please review below screenshots.

Currently i have below table.

And i need to update 'slug' column like below screenshot.

Answer 1

Assuming the suburb_id is autoincremental primary key:

WITH cte AS ( SELECT suburb_id, 
                     ROW_NUMBER() OVER ( PARTITION BY LOWER(locality) 
                                         ORDER BY suburb_id ASC ) rn
              FROM tableA )
UPDATE tableA, cte
SET tableA.slug = CASE WHEN cte.rn = 1 
                       THEN LOWER(tableA.locality)
                       ELSE CONCAT( LOWER(tableA.locality), '_', cte.rn-1 )
                       END
WHERE tableA.suburb_id = cte.suburb_id

> are you sure that WITH ... UPDATE ... has been implemented in MariaDB? – ypercubeᵀᴹ

If not, then simply convert CTE to subquery:

UPDATE tableA, 
       ( SELECT suburb_id, 
         ROW_NUMBER() OVER ( PARTITION BY LOWER(locality) 
                             ORDER BY suburb_id ASC ) rn
         FROM tableA ) cte
SET tableA.slug = CASE WHEN cte.rn = 1 
                       THEN LOWER(tableA.locality)
                       ELSE CONCAT( LOWER(tableA.locality), '_', cte.rn-1 )
                       END
WHERE tableA.suburb_id = cte.suburb_id

Answer 2

You can create the below procedure and it will update all empty slug, it won't create duplicate slugs.

DROP TABLE IF EXISTS `suburb`;

CREATE TABLE IF NOT EXISTS `suburb` (
  `suburb_id` int(11) NOT NULL AUTO_INCREMENT,
  `locality` varchar(255) DEFAULT NULL,
  `slug` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`suburb_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

INSERT INTO `suburb` (`suburb_id`, `locality`, `slug`) VALUES
(1, 'post One', ''),
(2, 'post', ''),
(3, 'post two', ''),
(4, 'post three', ''),
(5, 'posts', ''),
(6, 'post four', ''),
(7, 'post', ''),
(8, 'post five', ''),
(9, 'post Six', ''),
(10, 'posts', ''),
(11, 'post one', ''),
(12, 'post two', ''),
(13, 'post seven', ''),
(14, 'posts', ''),
(15, 'Post', '');

Now Create Procedure:

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE new_slug varchar(100);
DECLARE var_locality varchar(100);
DECLARE slug_with_id varchar(100);
DECLARE slug_id INT;
DECLARE cur CURSOR FOR SELECT `suburb_id`,`locality`from suburb WHERE `slug` = "";
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   OPEN cur;
       ins_loop: LOOP                                   
           FETCH cur INTO slug_id,var_locality;                     
           SET new_slug = REPLACE(var_locality," ","_");
           SET new_slug = LOWER(new_slug);            
           SET slug_with_id = CONCAT(new_slug,"_",slug_id);            
           select slug_id,var_locality,new_slug,slug_with_id;

           IF((SELECT COUNT(*) FROM suburb WHERE `slug` = new_slug AND `suburb_id` != slug_id) > 0) THEN 
               UPDATE `suburb` SET `slug` = slug_with_id WHERE `suburb_id` = slug_id;
           END IF;

           IF((SELECT COUNT(*) FROM suburb WHERE `slug` = new_slug AND `suburb_id` != slug_id) = 0) THEN 
               UPDATE `suburb` SET `slug` = new_slug WHERE `suburb_id` = slug_id;
           END IF;

           IF done THEN
               LEAVE ins_loop;
           END IF;                                                      
       END LOOP;
   CLOSE cur;
END

Once this is done go to mysql|mariadb console

mysql> Call PROCDURE_NAME;