mysql - update each value of a concatenated string

Question

This questions follow this solved, but now I would like to make an update directly

I have a table like this

id | cod | type        | qta
-: | :-- | :---------- | --:
 1 | aaa | aaa,bbb,ccc |   3
 2 | aaa | ddd         |   1
 3 | aaa | eee,fff     |   4
 4 | aaa | ggg,hhh     |   2
 5 | aaa | iii         |   7
 6 | aaa | lll         |   7

and I would like to have a result like this

id | cod | type              | qta
-: | :-- | :----------       | --:
 1 | aaa | aaa_1,bbb_1,ccc_1 |   3
 2 | aaa | ddd_1             |   1
 3 | aaa | eee,fff           |   4
 4 | aaa | ggg_1,hhh_1       |   2
 5 | aaa | iii               |   7
 6 | aaa | lll               |   7

I want to update each word in the string, where number of words in the string are the same of 'qta'.

To get the rows I use

select *
from tab1
where char_length(type) - char_length(replace(type, ',', '')) + 1 = qta

but I would like to have:

update tab1
SET type = ...
where char_length(type) - char_length(replace(type, ',', '')) + 1 = qta;

Here the link to make the test

Answer 1

You can try something like below,

UPDATE tab1
        JOIN
    (SELECT 
        id, CONCAT(REPLACE(type, ',', '_1,'), '_1') AS type
    FROM
        tab1
    WHERE
        CHAR_LENGTH(type) - CHAR_LENGTH(REPLACE(type, ',', '')) + 1 = qta) val ON tab1.id = val.id 
SET 
    tab1.type = val.type;