Revision history [back]

To use a lookup table in SQL to replace the values in a column with new ones, you can follow these steps:

1. Create a lookup table that contains two columns - the old value and the new value. For example, if you want to replace the values in the "gender" column with new ones, you can create a lookup table that has two columns - "M" and "Male", and "F" and "Female".

2. Join the original table with the lookup table on the column that you want to replace. For example, if the original table is called "users" and the column you want to replace is "gender", you can join "users" with the lookup table on "gender".

3. Select the new value from the lookup table and update the original table with the new value. For example, if you want to replace "M" with "Male", and "F" with "Female", you can update the "gender" column in "users" with the new values using the following SQL query:

UPDATE users SET gender = CASE WHEN lookup.oldvalue = 'M' THEN 'Male' WHEN lookup.oldvalue = 'F' THEN 'Female' END FROM lookup WHERE users.gender = lookup.old_value;

This query uses a CASE statement to replace the old values with the new values based on the values in the lookup table. The WHERE clause ensures that the update only affects the rows where the old value matches a value in the lookup table.