Eliminate null values in mysql

Question

I have this kind of table:

Column 1 | Column 2 | Column 3 | Column 4
----------------------------------------
Value 1  |  null    | null     | null    
null     |  Value 2 | null     | null    
null     |  null    | Value 3  | null  
null     |  null    | null     | Value 4

I want to eliminate null values. I want it to be like this:

Column 1 | Column 2 | Column 3 | Column 4
----------------------------------------
Value 1  |  Value 2 | Value 3  | Value 4

Any help will be much appreciated. Thanks.

What would you expect to happen if there were more than four rows in the table, or if one of the rows had more than one value defined? — user149341
– user149341, Commented Oct 1, 2018 at 17:43
What have you tried so far..? Not even DELETE FROM table WHERE [Column 1] IS NULL AND [Column Two] IS NULL AND.... you get the picture. — Stuart
– Stuart, Commented Oct 1, 2018 at 17:43

Yogesh Sharma · Accepted Answer · 2018-10-01 17:45:06Z

3

It seems you want aggregation :

select max(col1), max(col2), max(col3), max(col4)
from table t;

Assuming you have a supportive column if so, then you can do :

select col, max(col1), max(col2), max(col3), max(col4)
from table t
group by col;

edited Oct 1, 2018 at 17:45

answered Oct 1, 2018 at 17:42

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

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

1 Comment

T Gray Over a year ago

The only time this won't work is if there are more values in any of the columns. At that point, in order to make it work, you will have to have a common column to group by as in the second example above.

Collectives™ on Stack Overflow

Eliminate null values in mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related