How to merge null values with not null values of several columns in mysql table?

Question

I have the following example table:

 || *Column 1* || *Column 2* || *Column 3* || *Column 4* || 
 ||     a      ||   null     ||     b      ||     a      || 
 ||     b      ||     f      ||    null    ||     f      ||
 ||   null     ||     a      ||     a      ||     b      ||

The result table has to be:

 || *Column 1* || *Column 2* ||
 ||     a      ||     b      ||
 ||     b      ||     f      ||
 ||     a      ||     b      ||

Thanks!

are there any chances that all columns have difference values? — John Woo
– John Woo, Commented Apr 17, 2013 at 8:44
Let me get this right. Your new Column 1 is the 1st and 2nd value of old Column 1 and 3rd of old Coulmn 2, and new Column 2 is the 1st of Column 3 and 2nd and 3rd of Column 4 — CoBolt
– CoBolt, Commented Apr 17, 2013 at 8:45
I would do it with a script of some sort. Select all values from first table, do some script magic to sort out duplicates and null values, then insert into the second table. Doing it with queries or procedures only seems like a pain in the ... — Jakob
– Jakob, Commented Apr 17, 2013 at 8:46
If in the first column there is a null value, then it has to be replaced with the first next not null value from the other columns. So, this problem is not about 2nd or 3rd columns. I need some kind of general solution. — ArmMiner
– ArmMiner, Commented Apr 17, 2013 at 9:05

Robin Castlin · Accepted Answer · 2013-04-17 08:55:00Z

4

SELECT one,
    CASE WHEN one != two THEN two
        WHEN one != three THEN three
        WHEN one != four THEN four END AS two
FROM (  SELECT COALESCE(col1, col2, col3, col4) AS one,
            COALESCE(col2, col3, col4, col1) AS two,
            COALESCE(col3, col4, col1, col2) AS three,
            COALESCE(col4, col1, col2, col3) AS four
        FROM four_columns) AS h

Can't think of a simplier solution considering what result you wish in the example.

This obviously obsoletes cases where these 4 columns have more than 2 different letters, due to only 2 columns existing. But it won't show duplicates and will not show NULL if there is at least 2 unique letters.

MySQL COALESCE()

edited Apr 17, 2013 at 8:55

answered Apr 17, 2013 at 8:49

Robin Castlin

11k1 gold badge30 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ArmMiner Over a year ago

Why are you using CASE ? What if I only use COALESCE()?

Robin Castlin Over a year ago

Else on the third row in your example you'd get double a and no b. Basicly it makes sure you don't get double letters and more importantly, you don't fetch the same column value twice.

Community · Accepted Answer · 2017-05-23 12:31:14Z

1

If I understand you well, what you want to do is merge Column 3 with Column 1, and Column 4 with Column 2.

The following problem has already been answered on this post.

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Apr 17, 2013 at 8:46

Sarah Monod

1108 bronze badges

Collectives™ on Stack Overflow

How to merge null values with not null values of several columns in mysql table?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related