Question 237750 in Mysql Question posted in
The official MySQL documentation can be found here.

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:

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_.... so on

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

Note: Here mainproductId will be always 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?

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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    As pointed out in the comments by Shadow, you really should normalise your data. Serializing multiple values into a single column, is almost always a bad idea. Managing the data and querying the individual values is cumbersome and cannot make use of available indices. This will have a negative impact on performance as your dataset grows.

    Take this crude example, as we do not know how your sample data sits in relation to other data. If you want to find all tbl_seller_products with optionId = 137 you would need to use something like:

    SELECT *
FROM tbl_seller_products
WHERE (selprod_code LIKE '%_137_%' OR selprod_code LIKE '%_137')

    This query cannot use any indices so will have to do a full table scan. Whereas with the data normalised, you can use something like:

    SELECT selprod_id, prod_id
FROM seller_products
WHERE option_id = 137;

    which will be able to read directly from the index (or clustered PK) depending on the structure.

    This is a variation on the accepted answer, using a join instead of unions.

    UPDATE tbl_seller_products
JOIN (
    SELECT selprod_id, GROUP_CONCAT(optionId ORDER BY n = 1 DESC, optionId ASC SEPARATOR '_') AS selprod_code
    FROM (
        SELECT selprod_id, n, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(sp.selprod_code, '_', seq.n), '_', -1) AS UNSIGNED) AS optionId
        FROM tbl_seller_products sp
        JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) seq
            ON CHAR_LENGTH(sp.selprod_code) - CHAR_LENGTH(REPLACE(sp.selprod_code, '_', '')) >= seq.n - 1
    ) t1
    GROUP BY selprod_id
) t2
    ON tbl_seller_products.selprod_id = t2.selprod_id
SET tbl_seller_products.selprod_code = t2.selprod_code;

    Here’s a db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search