CODEWITHSUNDEEP
mysqldatabase
user2675590
user2675590

Reputation: 21

Changing a value using case

I'm migrating a database and I need to change a column which is in integers to their associated category in string format. How can I check each value and change it on the fly using ifelse or switch/case/break?

SELECT *, Category FROM Members
  Category =
  Switch(category)
  if (value == 1 then change to "Category 1", value == 2 then change to "Category 2 and so on.)

Upvotes: 2

Views: 51

Answers (2)

juergen d
juergen d

Reputation: 204894

SELECT *, case when category = 1 then 'Category 1'
               when category = 2 then 'Category 2'
               else 'Category unknown'
          end as category
FROM Members

Upvotes: 1

theftprevention
theftprevention

Reputation: 5213

MySQL's CASE clause can do this.

UPDATE `Members`
    Set `Category` = CASE `Value`
        WHEN '1' THEN 'Category 1'
        WHEN '2' THEN 'Category 2'
    END
WHERE `Value` IN ('1','2');

This means that any and all rows with Value = '1' will now also have Category = 'Category 1'. You can make this query more specific as necessary.

Upvotes: 0

Related Questions