RkdL RkdL - 5 months ago 20
SQL Question

Replace values in column using a corrections table

I'm looking for a solution to replace values within a column using a corrections table i can later add information to.

Here's the idea:

db.products contains data that needs to be

REPLACED


materials | unique_id |

leather | a8sd83k2m3 |
leater | b23k4j244w |
Coton | 8asdh3k4er |
Plyurethan| h8dhekjnrt |


db.corrections

id | correct_value | wrong_value1 | wrong_value2 | etc..
1 | Leather | leather | leater | (future miss-spellings)
1 | Cotton | Coton | cotonn | (future miss-spellings)
1 | Polyurethane | Plyurethan | Plyurethane | (future miss-spellings)
1 | Wool | woool | wo | (future miss-spellings)


or if maybe faster/better solution:

db.corrections

id | correct_value | wrong_value
1 | Leather | leather,leater,(future miss-spellings)
1 | Cotton | Coton,cotonn,(future miss-spellings)
1 | Polyurethane | Plyurethan,Plyurethane,(future miss-spellings)
1 | Wool | woool,wo,(future miss-spellings)


What i am working with now is an
UPDATE
statement with multiple
REPLACE
but these statements are making really long query's and are just a mess, calling a material by using it's id would be much more efficient imho. I.E.:

UPDATE db.product SET material = REPLACE(REPLACE(REPLACE(material,


What would be the right way to go and how would such a query look like? Am i thinking in the right direction (below) or do i need something completely different?

db.corrections AS c
UPDATE db.product set material = REPLACE (material, searchvalues, replacevalues)
WHERE searchval = c.wrong_value1 OR searchval = cwrong_value2;


I will be trying more later today so i can't accept an answer sooner than this evening. Looking very much forward to your ideas.

Answer

You don't have to join products on column value with corrections, just join all rows

id | correct_value | wrong_value
1  | Leather       | leather
2  | Leather       | leater
3  | Cotton        | Coton
4  | Cotton        | cotonn
5  | Polyurethane  | Plyurethan
6  | Polyurethane  | Plyurethane
7  | Wool          | woool
8  | Wool          | wo

 UPDATE product p, corrections c
 set p.material = REPLACE(p.material, c.wrong_value, c.correct_value);

A little bit more safe, because you only want to replace whole words (e.g. wo can be part of an other word:

UPDATE product p, corrections c
 set p.material = REPLACE(p.material, CONCAT(' ', c.wrong_value, ' '), CONCAT(' ', c.correct_value, ' '));