0

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. enter image description here

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 
  • Strange. You have digits for some conversions and words for others. How do you decide? And fields with "posts" (i.e. an "s" at the end of posts) goes from 1 to 3 and then 4 - why? – Vérace Sep 4 '19 at 7:32
0

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).
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.