Update is not updating the whole string

Question

Following is my SQL fiddle: http://sqlfiddle.com/#!2/f9bae/1

In which i am trying to check if the whole name is being verified by lookup table or not and if there is any error then it should get replaced by the correct value but the problem i am facing is that if any name contains more than one wrong values then query only update one part of that name and leave the rest unchanged , kindly let me know how can i modify my query for update so it'll update the whole name as per lookup table correct values.

Thanks,

Answer 1

Here is a SQLFIddle demo

This UGLY query works for maximum 4 replacements in a string. If you need more replacements the best way is to create a User Defined Function to replace these words in one string and use it to update.

 UPDATE table1 a 
  LEFT JOIN lookup b1 on a.username LIKE CONCAT('%', b1.`WRONG`, '%')
  LEFT JOIN lookup b2 on a.username LIKE CONCAT('%', b2.`WRONG`, '%') 
            and (b2.Wrong not in (b1.Wrong))
  LEFT JOIN lookup b3 on a.username LIKE CONCAT('%', b3.`WRONG`, '%') 
            and (b3.Wrong not in (b1.Wrong,b2.Wrong))
  LEFT JOIN lookup b4 on a.username LIKE CONCAT('%', b4.`WRONG`, '%') 
            and (b4.Wrong not in (b1.Wrong,b2.Wrong,b3.Wrong))

SET a.username = 
REPLACE(
  REPLACE(
      REPLACE(
           REPLACE(a.username, IFNULL(b1.`WRONG`,''), IFNULL(b1.`RIGHT`,''))
        , IFNULL(b2.`WRONG`,''), IFNULL(b2.`RIGHT`,''))
    , IFNULL(b3.`WRONG`,''), IFNULL(b3.`RIGHT`,''))
  , IFNULL(b4.`WRONG`,''), IFNULL(b4.`RIGHT`,''))