CODEWITHSUNDEEP
phpmysql
Edward144
Edward144

Reputation: 493

MySQL Create a Find and Replace Lookup Table

I need to create a way to automatically shorten certain values in a table in MySQL.

What I would like to do is create a table which will have three columns. The 'Original Value' and 'New Value' and an ID. Then use a query to check if a column in 'Table_A' exists in this 'Original Value' and change it to the corresponding 'New Value'.

The query I've used to find out if the value exists:

SELECT EXISTS (SELECT Reference FROM table_a) FROM value_lookup WHERE Original_Value != '';

Lets say 'Reference' contains 'This is a value'. 'Original_Value' contains 'This is a value' and 'New Value' contains 'Value'.

How can I now copy the 'New Value' to the 'Reference' in Table_A?

The only way I can think of is by selecting the new value where ID = x and copying it into reference in table_a where ID = y. But it doesn't seem the most efficient.

Upvotes: 1

Views: 308

Answers (1)

dubes
dubes

Reputation: 5524

In Databases, it can be much more efficient to just do the operation in bulk. Your problem statement is that you want to replace the original value in some table (let us call it source) with new value in some other table (let us call it lookup) where the reference_value in lookup table matches the original table.

That would translate to the following SQL statement: 

update source a
    left join lookup b on
       a.originalValue = b.referenceValue
set
    a.originalValue = b.newValue
where 
    a.originalValue != b.newValue

I am not 100% sure of mysql syntax, but I hope you get the gist. Databases are usually very adept at such operations, so performance should not be a problem as well.

You can of course alter the clauses to fit your needs, for e.x. case insensitive match with Lookup, ignoring null values etc.

Upvotes: 1

Related Questions