Update column value based on other column with manage duplicate value MySQL query 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.
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