Geek Answers Handbook

Update does not update the entire row

Below is my SQL script: http://sqlfiddle.com/#!2/f9bae/1

In which I am trying to check if all the name in the lookup table is checked or not, and if there is any error, then it should be replaced with the correct value, but the problem I encountered is that if any the name contains more than one incorrect value, then the request only updates one part of this name and leaves the rest unchanged, kindly tell me how I can change my update request so that it updates the entire name in accordance with the correct values ​​of the correspondence table.

Thanks,

+7
source share
2 answers

Here is the SQLFIddle demo

This UGLY query works for a maximum of 4 replacements per line. If you need more replacements, the best way is to create a custom function to replace these words on one line 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`,''))
+2
source

With your setup, it might be easier to just run the update up to 3 times, which will handle the repetitions. On the side, the note chaudhary or choudhary is perhaps more correct than hodrim :)

http://sqlfiddle.com/#!2/2d4c6/1

EDIT: Alternatively, you can get rid of the lookup table if you don't have too many entries, for example:

http://sqlfiddle.com/#!2/890ea/1

0
source

More articles:


All Articles