Ask Your Question
3

How can one use a lookup table in sql to replace the values in a column with new ones?

asked 2023-05-26 14:41:28 +0000

qstack gravatar image

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
3

answered 2023-05-26 15:00:02 +0000

lalupa gravatar image

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.

edit flag offensive delete link more

Your Answer

Please start posting anonymously - your entry will be published after you log in or create a new account. This space is reserved only for answers. If you would like to engage in a discussion, please instead post a comment under the question or an answer that you would like to discuss

Add Answer


Question Tools

Stats

Asked: 2023-05-26 14:41:28 +0000

Seen: 20 times

Last updated: May 26 '23