Anthony Anthony - 6 days ago 5
MySQL Question

How to update the 3rd element of nested elements (JSON like)?

I currently have a MySQL table as follow:

|| ID || Name || handling || enabled ||
|| 1 || bob || { 2 { 4, 7, 0.2 } 7 { 20.102, 3 } } || 1 ||
|| 2 || abc || { 6 { 4, 9, 0.6 } 7 { 20.102, 83 } } || 1 ||
|| 3 || xyz || { 2 { 4, 78, 0.2 } 7 { 20.102, 3 } } || 1 ||


I'm trying to find a way to do the following trick via an SQL query:

The third number there (7 for id 1, 9 for id 2, 78 for id 3) has to be changed to '30'. I'd do it all manually, but it's a table of approx. 5000 rows. And I "could" make a loop in c++ to do it all, but for some technical reasons, I rather have a sql query.

I have no clue!
Halp!

Answer

If you know that the part to replace is always between first and second comma then you can do some string slicing, like this:

to change text between 1 and 2 comma:

UPDATE TEST_TABLE6
SET A = CONCAT(SUBSTRING_INDEX(A,',',1),',','30',SUBSTRING(A, LENGTH(SUBSTRING_INDEX(A,',',2))+1));

to change text between 7 and 8 comma:

UPDATE TEST_TABLE6
SET A = CONCAT(SUBSTRING_INDEX(A,',',7),',','30',SUBSTRING(A, LENGTH(SUBSTRING_INDEX(A,',',8))+1));
Comments