CODEWITHSUNDEEP
mysqlsql
soft genic
soft genic

Reputation: 2056

Data should get replace as per lookup table

Following is my sqlfiddle for testing and building the query:

http://sqlfiddle.com/#!2/975c6/1

What I have been trying to do is to detect if Table1 column ADRES `house# 240 st. # 76 Cantt. road Chicago contains any value present in wrong column in lookup table that should get replaced by right column value.

Table1:

Username | Adres
Jhon     | house#  240 st. #  76 Cantt. road Chicago

Look up table:

WRONG   |    Right
house#       HNO

For example : house# 240 st. # 76 Cantt. road Chicago (house# is wrong so it should gets replaced by HNO)and update Table 1 and set address to

Jhon     | HNO  240 st. #  76 Cantt. road Chicago

Upvotes: 0

Views: 167

Answers (1)

John Woo
John Woo

Reputation: 263723

if you want to update the fields,

 UPDATE table1 a CROSS JOIN lookup b
 SET    a.ADRES = REPLACE(a.ADRES, b.`WRONG`, b.`RIGHT`)
 WHERE  a.ADRES LIKE CONCAT('%', b.`WRONG`, '%')

but if you want only to project the replaced value,

 SELECT  a.Username, REPLACE(a.ADRES, b.`WRONG`, b.`RIGHT`)
 FROM    table1 a CROSS JOIN lookup b
 WHERE   a.ADRES LIKE CONCAT('%', b.`WRONG`, '%')

Upvotes: 1

Related Questions