update a part of field using matching id from another table

I have table with following data (here key field comprises of site__marker)

  id     userid              key                value
   1       1                site_1_name         http://example1.com
   2       1                site_1_user         user1
   3       1                site_2_name         http://example2.com
   4       2                site_1_name         http://example3.com
   5       2                site_1_user         user2

and I have site mapping table

 oldsiteid      newsiteid
     1              120
     2              152

Now I need to update first table in way that only values updates in key field should match oldsiteid in second table, and should get updated by newsiteid

Outpu开发者_运维知识库t should be like

  id     userid              key                value
   1       1                site_120_name         http://example1.com
   2       1                site_120_user         user1
   3       1                site_152_name         http://example2.com
   4       2                site_120_name         http://example3.com
   5       2                site_120_user         user2

How to achieve this?

You will have to translate this REXX in SQL, the functions are staight forward:

old = 'user_1_ddd' 
n = "333"
new = substr(a,1,index(a,"_")) || n || substr(a,index(a,"_") + index(substr(a, index(a,"_")+1)  ,"_")) 

results in user_333_ddd

substr is the same in both

for index, use Find_in_set

for || use concat

I do not have MySQL but this should work:

UPDATE  TargetTable
SET key = CONCAT
    ( 
        SUBSTRING_INDEX(key,'_',1)
        ,'_'
        , (SELECT newsiteid FROM MappingTable WHERE MappingTable.oldsiteid =  SUBSTRING_INDEX(SUBSTRING_INDEX(TargetTable.key,'_',-2), '_', 1 ))
        ,'_'
        ,SUBSTRING_INDEX(key,'_',-1) 
    )