Update column based on other column with concat by underscore and manage duplicate value MySQL query for mysql 5.7.27

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.

I used below sql query but it will not worked on MYSQL5.7 So please help me to convert below query with MYSQL5.7

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 replace(LOWER(tableA.locality), ' ', '_')
                       ELSE CONCAT( replace(LOWER(tableA.locality), ' ', '_'), '_', cte.rn-1 )
                       END
WHERE tableA.suburb_id = cte.suburb_id

I know that PARTITION BY and OVER() function not worked into MYSQL5.7 So, please me to create query for above.

Now i created below query.

UPDATE tableA, 
( 
SELECT 
    @row_number:=CASE
        WHEN @customer_no = locality 
          THEN 
              @row_number + 1
          ELSE 
               1
        END AS rn,
    @customer_no:=locality locality,
    id
FROM
    tableA,
    (SELECT @customer_no:=0,@row_number:=0) as t ORDER BY locality) cte
SET tableA.slug = CASE WHEN cte.rn = 1 
                       THEN replace(LOWER(tableA.locality), ' ', '_')
                       ELSE CONCAT( replace(LOWER(tableA.locality), ' ', '_'), '_', cte.rn-1 )
                       END
WHERE tableA.suburb_id = cte.suburb_id

I used above query but not worked properly. Inner SELECT query worked fine and get right row number for all locality with duplicate value. But Update query not properly work. I don't know how to manipulate with right connection for update and inner select query. Please help me.

I need to output like below :

subrub_id |  locality | slug
-----------------------------
1         |  Post one  | post_one    
2         |  Post TWO  | post_two
3         |  Post Three| post_three
4         |  Posts     | posts
5         |  Post One  | post_one_1
6         |  Posts     | posts_1         
7         |  Post Four | post_four
8         |  Post Five | post_five
9         |  Post Ten  | post_ten
10        |  Posts     | posts_2
11        |  Post Two  | post_two
12        |  POSTS     | post_3

Solved this by me. See below query if help to others.

UPDATE tableA
JOIN
(
SELECT cte.subrub_id as main_id, cte.post_name, cte.rn, CASE WHEN cte.rn = 1
                       THEN replace(LOWER(tableA.locality), ' ', '-')
                       ELSE CONCAT( replace(LOWER(tableA.locality), ' ', '-'), '-', cte.rn-1 )
                       END AS a  FROM tableA,
(
SELECT
    @row_number:=CASE
        WHEN @customer_no = locality
          THEN
              @row_number + 1
          ELSE
               1
        END AS rn,
    @customer_no:=locality post_name,
    id
FROM
    tableA,
    (SELECT @customer_no:=0,@row_number:=0) as t ORDER BY locality ASC  
        LIMIT 0, 10000 ) as cte
WHERE tableA.subrub_id = cte.subrub_id ORDER BY cte.post_name ASC
)
as t
ON tableA.subrub_id = t.main_id  
SET tableA.slug =  t.a 

Answer 1

akina: You need to emulate ROW_NUMBER(). There are 2 general ways:

1. Subquery which uses 2 table references (see, for example, Order by is ignored if inside a derived table of a update, with the correction that the subquery must be moved to main query's UPDATE clause); and
2. Subquery which uses user-defined variables (see, for example, query to replace text with sequential numbers).