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 != '';
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
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.