mysql update nested one field

Question

I have the following problem. I need to make replacements in a field with values from another table. Example :

Table1

id     |   value
=====================================
AAAAA    '10,40,100,200,300,400,500,600'


Table2

valueold | valuenew
===================
10          95
40          30
500         250

The expected result :

id     |   value
=====================================
AAAAA    '95,30,100,200,300,400,250,600'

Mysql query is :

update table1 as t1 inner join (select valueold, valuenew from table2) as t2
on find_in_set(t2.valueold,t1.value) 
set value = (select result from 
(SELECT id,value,valueold,valuenew, 
trim(',' from concat_ws(',',substring_index(value,',',find_in_set(valueold,value)-1)
,valuenew, substring(value,length(substring_index(value,',',find_in_set(valueold,value)) )+2) )) as result
FROM table1 as st1 inner join (select valueold, valuenew from table2) as st2
on find_in_set(st2.valueold,st1.value)) as tresult
where tresult.id = t1.id and tresult.valueold=t2.valueold and tresult.valuenew=t2.valuenew
)

This query only update de first record ... Any ideas. Thanks

Please check this answer: http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match Hope it helps. — latsi, Aug 25 '15 at 14:34

score 0 · Answer 1 · answered Aug 26 '15 at 07:59

I modified your query a bit. This seems to work ok for a specific id, maybe you could put it inside a function and call it for each id in table1.

SELECT @runtot := value from table1 WHERE id = 'AAAAA';
SET @counter = 0; 
SELECT (@counter := @counter +1) AS counter, id,value,valueold,valuenew, 
@runtot := trim(',' from concat_ws(',',substring_index(@runtot,',',find_in_set(valueold,value)-1)
,valuenew, substring(@runtot,length(substring_index(@runtot,',',find_in_set(valueold,@runtot)) )+2) )) as result
FROM table1 as st1 inner join (select valueold, valuenew from table2) as st2
WHERE st1.id = 'AAAAA'
ORDER BY @counter DESC
LIMIT 1;

score 0 · Answer 2 · answered Aug 26 '15 at 10:11

Possible solution for all ids.

select @id:=id,@val:=value, (select @val:=trim(',' from concat_ws(',',substring_index
    (@val,',',find_in_set(valueold,@val)-1),valuenew,substring(@val,
    length(substring_index(@val,',',find_in_set(valueold,@val)) )+2) )) as result
    from table1 inner join table2 on find_in_Set(valueold,@val)
    where id=@id
    order by valueold asc
   limit 0,1
) as sub,
@id,@val
from table1

The result of subquery "sub" is not good but @val at end its good, i suppose that is recalculated in subquery. This sentence for update table is very slow but run.

mysql update nested one field

2 Answers2