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

mariadb-10.3MySQL

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.

enter image description here

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

Best Answer

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
Related Question