Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to combine mutually exclusive columns in MySQL

Tags:

sql

mysql

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

like image 676
alex_the_emperor Avatar asked Jun 13 '16 07:06

alex_the_emperor


People also ask

How do I merge two columns in a new column in SQL?

try to use coalesce() and concat() to combine columns in the SQL query. Eg: 1, vishnu, 9961907453, 91 will return phone number as +919961907453. You can use the following query to get the above result.

How do I intersect two columns in SQL?

Similar to the UNION operator in SQL, even the INTERSECT operator has to abide by the following two rules for successful execution: The number and order of columns in both queries has to be the same. The data types of corresponding columns from both the select queries must be compatible with each other.

Can you group by multiple columns in MySQL?

The GROUP BY clause is used along with some aggregate functions to group columns with the same values in different rows. The group by multiple columns technique retrieves grouped column values from one or more database tables by considering more than one column as grouping criteria.


1 Answers

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

like image 169
Jim Garrison Avatar answered Sep 29 '22 19:09

Jim Garrison