There is a ForgeRock table that contains a name field. In the ForgeRock.name field, you need to replace the substring from ReplaceData.cut with data from the ReplaceData.paste field

 CREATE TABLE ForgeRock (`id` int, `name` varchar(77), `description` varchar(55) ); INSERT INTO ForgeRock (`id`, `name`, `description`) VALUES (1, 'OpenIDM', 'Platform for building enterprise provisioning for solutions'), (2, 'OpenAM', 'Full-featured access management'), (3, 'OpenDJ', 'Robust LDAP server for Java'); CREATE TABLE ReplaceData (`id` int, `fieldNaame` varchar(77), `f2` varchar(55), `f2` varchar(55) ); INSERT INTO ReplaceData (`id`, `cut`, `paste`) VALUES (1,,'Open', 'Close'), (2,'IDM', 'xxxxx');

After working out "our" replacement, you should get something like this

 (1, 'Closexxxxx', 'Platform for building enterprise provisioning for solutions'), (2, 'CloseAM', 'Full-featured access management'), (3, 'CloseDJ', 'Robust LDAP server for Java');

The first thing that comes to mind:

 UPDATE `ForgeRock` d, `ReplaceData` t SET d.`name` = replace(d.`name`, t.`cut`, t.`paste`)

Second:

 UPDATE `ForgeRock` d, `ReplaceData` t SET d.`name` = replace(d.`name`, t.`cut`, t.`paste`) where d.`name` LIKE CONCAT('%', t.`cut` ,'%');

I do not know further.

  • So what's the problem? The first query seems to do what you need. - Ksenia
  • @Ksenia, it changes only the data in the first row of the ReplaceData table, ReplaceData , only Open to Close - Sergey Sereda
  • Within one request, the task is not solved. Write HP - prepared statement with parameters, the cursor on ReplaceData, piece replacement. - Akina

1 answer 1

Query from the discharge of magic variables:

 update `ForgeRock` u join ( select name, new from ( select d.*, @cur:=if(@grp=d.name,@cur,d.name), @cur:=replace(@cur, t.`cut`, t.`paste`) new, @n:=if(@grp=d.name,@n+1,1) N, @grp:=d.name from `ForgeRock` d, `ReplaceData` t, (select @cur:='',@grp:='',@n:=0) A order by d.name ) X where N=@n ) N on u.name=N.name set u.name=N.new

In the subquery, the data is sequentially replaced with each new line. @grp is used to memorize the current name being processed and to obtain a new value for @cur (the current state of the replaced string) when moving to the next name. @n numbers records, all replacements are completed when the current line number is equal to the number of replacement lines. And the number of replacement rows remains in the @n variable after executing the deepest subquery, and in an external query you can already compare the row number with the maximum ( where N=@n ). It remains to glue it to the table being changed and replaced.

Sample on sqlfiddle.com

  • one
    , that's magic!)) - Ksenia