Recode column values in MySQL

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!

Answer

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);