I am dealing with a column named UCODE130 that has values anywhere from 1-130. I would like to replace the values with ranges. For example, I want to replace any 1, 2, 3, or 4 found in that column with a string "1 - 4" instead. I do not want to do just a select statement - I want to actually change the column in the database itself.
The other issue I have is that these ranges are all different sizes. I would need something like:
1,2,3,or 4 becomes "1 - 4"
5,6 becomes "5 - 6"
7,8,9,10,11,or 12 becomes "7 - 12"
and so on.
I need to recode or "find and replace" the values to get this to work. There are over 20,000 rows in my column so I was hoping there was an efficient way to do this. Please let me know if more information is needed. Thank you!
You can define the sizes with a CASE expression in the UPDATE statement:
UPDATE tablename
SET UCODE130 = CASE
WHEN UCODE130 BETWEEN 1 AND 4 THEN '1 - 4'
WHEN UCODE130 BETWEEN 5 AND 6 THEN '5 - 6'
WHEN UCODE130 BETWEEN 7 AND 12 THEN '7 - 12'
END
WHERE UCODE130 BETWEEN 1 AND 12
or:
UPDATE tablename
SET UCODE130 = CASE
WHEN UCODE130 <= 4 THEN '1 - 4'
WHEN UCODE130 <= 6 THEN '5 - 6'
ELSE '7 - 12'
END
WHERE UCODE130 BETWEEN 1 AND 12
Note that for this to work the column's data type must be VARCHAR.
If it is INTEGER, first change its data type:
ALTER TABLE tablename MODIFY id VARCHAR(10);