mysql

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;