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