Separating String list and replacing same list with new values in mysql I have following data in my table_1 Table table_1(Currently saved structure)
code value
12_A ["A","B","C","D"]
12_B ["E","F","G","H"]
12_3 ["I","J","K","L"]
But each code have different values with different description. like::
code value description
12_A A Apple
12_A B Ball
12_A C Cat
12_A D Dog
12_B E Eagle
12_B F Flag
. . .
. . .
. . .
I have to Separate the value list from table_1 and need to save again in same table i.e table_1(in this structure)::
code value
12_A ["Apple","Ball","Cat","Dog"]
12_B ["Eagle","Flag",.......]
12_3 [......................]
You can use GROUP_CONCAT() :
UPDATE Table1 s
SET s.value = (SELECT t.code,CONCAT('["',
GROUP_CONCAT(t.description ORDER BY t.description SEPARATOR '","'),
']')
FROM Table_With_val t
WHERE t.code = s.code
AND s.value LIKE CONCAT('%"',t.value,'"%'))
You didn't provide any conclusive information, I assumed the second data sample you provided is an existing table, and table1 is the table you want to update.
NOTE: This is a bad DB structure! it would most defiantly cause problem in the future especially when required to make joins . I strongly advise you to normalize your data and store each description and value in its own record.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments