How to combine mutually exclusive columns in MySQL

Question

I have a database with multiple mutually exclusive columns in it (don't ask me why, somebody created it like that. sigh). Below is an example of it.

-------------------------------------------
| ID | BLACK | WHITE | RED | GREEN | BLUE |
-------------------------------------------
| 1  | 1     | 0     | 0   | 0     | 0    |
-------------------------------------------
| 2  | 0     | 0     | 1   | 0     | 0    |
-------------------------------------------
| 3  | 0     | 1     | 0   | 0     | 0    |
-------------------------------------------

well you get the idea, it contains thousands of record. Now, how do I combine it into a single "COLOR" column. Like below.

--------------
| ID | COLOR |
--------------
| 1  | BLACK |
--------------
| 2  | RED   |
--------------
| 3  | WHITE |
--------------

all I can think of right now is to do 5 of these

UPDATE table SET COLOR = 'BLACK' WHERE BLACK = 1

oh I forgot to mention, the table that I am currently working on contains at least 15 mutually exclusive columns! are there any simpler ways I can do this?

Edit
changed COLORS to COLOR

Answer 1

Use a case-expression, as in this abbreviated example:

update table
  set color= 
    case
      when black then "black"
      when red   then "red"
      when blue  then "blue"
    end;

Here's a sqlfiddle