Mysql : get column value,explode it and make it ascending and then implode it back and save but through query only

I have a table like this (an old site table and I can't change the structure):

selprod_id  selprod_code
1857        421_92_137_296
1856        421_92_295_137
1855        421_92_296_138
1854        421_92_138_295
1853        421_93_137_296
1852        421_93_137_295

Here selprod_code is a combination of different options. The pattern of creation is:

<mainProducId>_<optionId1>_<optionId2>_<optionId3>_.....

Now if you look some of them are in ascending order of options and some or not.

For example: 421_92_295_137 is not in ascending order, it should be 421_92_137_295. Why? Read the note part.

Note: Here <mainProducId> will be always comes at first. Only options need to be ascending.

I have fixed the code to save this data in ascending order for new products. (As it is PHP code)

But how can I fix this for already added products in the database? (old-entries)

Is this possible in Mysql directly?

I have tried the below code taken from the reference

SELECT selprod_id, selprod_code,
    CAST(SUBSTRING_INDEX(selprod_code, '_', 1) AS UNSIGNED) AS mainproductId,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(selprod_code, '_', 2), '_', -1) AS UNSIGNED) AS optionId1,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(selprod_code, '_', 3), '_', -1) AS UNSIGNED) AS optionId2,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(selprod_code, '_', 4), '_', -1) AS UNSIGNED) as optionId3
FROM tbl_seller_products

But it's a SELECT query, while I want an UPDATE query. I am unable to find out how to start with the UPDATE query.

Could someone give me a pathway to achieve it?

Solution

First you can split out your selprod_code elements into separate columns, using something like this

SELECT selprod_id, selprod_code,
    CAST(SUBSTRING_INDEX(selprod_code, '_', 1) AS UNSIGNED) AS mainproductId,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(selprod_code, '_', 2), '_', -1) AS UNSIGNED) AS optionId1,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(selprod_code, '_', 3), '_', -1) AS UNSIGNED) AS optionId2,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(selprod_code, '_', 4), '_', -1) AS UNSIGNED) as optionId3
FROM mytable

(If you have more options, you may have to extend this)

Once you have the option values in separate columns - let's say we keep the output above as prodopts - you can stack them vertically using some union queries

    SELECT selprod_id, mainproductId, optionId1 AS optionId
    FROM prodopts
    UNION
    SELECT selprod_id, mainproductId, optionId2 AS optionId
    FROM prodopts
    UNION
    SELECT selprod_id, mainproductId, optionId3 AS optionId
    FROM prodopts

Finally you can re-order your options and put them back together using GROUP_CONCAT

SELECT selprod_id, CONCAT(mainproductId, '_', GROUP_CONCAT(optionId ORDER BY optionId ASC SEPARATOR '_')) AS selprod_code
FROM (
    SELECT selprod_id, mainproductId, optionId1 AS optionId
    FROM prodopts
    UNION
    SELECT selprod_id, mainproductId, optionId2 AS optionId
    FROM prodopts
    UNION
    SELECT selprod_id, mainproductId, optionId3 AS optionId
    FROM prodopts
) optlist
GROUP BY selprod_id, mainproductId

Putting it all together in an update statement:

WITH prodopts AS (
    SELECT selprod_id, selprod_code,
        CAST(SUBSTRING_INDEX(selprod_code, '_', 1) AS UNSIGNED) AS mainproductId,
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(selprod_code, '_', 2), '_', -1) AS UNSIGNED) AS optionId1,
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(selprod_code, '_', 3), '_', -1) AS UNSIGNED) AS optionId2,
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(selprod_code, '_', 4), '_', -1) AS UNSIGNED) as optionId3
    FROM mytable
)
UPDATE mytable
INNER JOIN (
    SELECT selprod_id, CONCAT(mainproductId, '_', GROUP_CONCAT(optionId ORDER BY optionId ASC SEPARATOR '_')) AS selprod_code
    FROM (
        SELECT selprod_id, mainproductId, optionId1 AS optionId
        FROM prodopts
        UNION
        SELECT selprod_id, mainproductId, optionId2 AS optionId
        FROM prodopts
        UNION
        SELECT selprod_id, mainproductId, optionId3 AS optionId
        FROM prodopts
    ) optlist
    WHERE optionId > 0
    GROUP BY selprod_id, mainproductId
) newlist ON mytable.selprod_id = newlist.selprod_id
SET mytable.selprod_code = newlist.selprod_code

If you're on a version of MySQL less than version 8, here's an equivalent query that doesn't use a CTE:

UPDATE mytable
INNER JOIN (
    SELECT selprod_id, CONCAT(mainproductId, '_', GROUP_CONCAT(optionId ORDER BY optionId ASC SEPARATOR '_')) AS selprod_code
    FROM (
        SELECT
            selprod_id,
            CAST(SUBSTRING_INDEX(selprod_code, '_', 1) AS UNSIGNED) AS mainproductId,
            CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(selprod_code, '_', 2), '_', -1) AS UNSIGNED) AS optionId
        FROM mytable
        UNION
        SELECT
            selprod_id,
            CAST(SUBSTRING_INDEX(selprod_code, '_', 1) AS UNSIGNED) AS mainproductId,
            CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(selprod_code, '_', 3), '_', -1) AS UNSIGNED) AS optionId
        FROM mytable
        UNION
        SELECT
            selprod_id,
            CAST(SUBSTRING_INDEX(selprod_code, '_', 1) AS UNSIGNED) AS mainproductId,
            CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(selprod_code, '_', 4), '_', -1) AS UNSIGNED) AS optionId
        FROM mytable
    ) optlist
    WHERE optionId > 0
    GROUP BY selprod_id, mainproductId
) newlist ON mytable.selprod_id = newlist.selprod_id
SET mytable.selprod_code = newlist.selprod_code;