How to replace a column with null values in mysql

Question

One of my columns in the table contains 'NULL' values and I would like to replace them with certain values.

I looked at this post: Replace nulls values in sql using select statement? and followed the answer.

My sql statement:

Select ifnull(`option`, 'MCQ') as `option` 
from question_table

This statement returns me the columns there are already with 'MCQ', but the 'NULL' values are not replaced yet.

Need some guidance to change this.

Check your data, is the option value really NULL or 'NULL'? — Tsanyo Tsanev
– Tsanyo Tsanev, Commented Jun 16, 2015 at 14:13
Are you filtering out data with some where condition ? This should return the null values as 'MCQ'. — Abhik Chakraborty
– Abhik Chakraborty, Commented Jun 16, 2015 at 14:13
I am not filtering out data. If i use the search functionality and put "IS NULL", I am able to get all the data out that have option as NULL. — lakshmen
– lakshmen, Commented Jun 16, 2015 at 14:15
@lakesh . . . How do you know whether the values are changing or not? Is that the entire query? Can you edit your question and provide sample data and the results you are getting with your query? — Gordon Linoff
– Gordon Linoff, Commented Jun 16, 2015 at 14:15
I click the database again and search if there is any NULL options. It shows it to me. — lakshmen
– lakshmen, Commented Jun 16, 2015 at 14:16

Gordon Linoff · Accepted Answer · 2015-06-16 14:17:47Z

5

If you want to change the data, you need an update:

update question_table
    set option = 'MCQ'
    where option is null;

A select statement does not change the database.

answered Jun 16, 2015 at 14:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

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

0

If you want to update the table use Gordon's answer, but maybe you just want to return a replacement value for NULL in the SELECT, you can use COALESCE:

SELECT COALESCE(`option`, 'MCQ') as `option` 
FROM question_table

This selects MCQ for every option-value that is NULL.

Another way is using CASE:

SELECT CASE WHEN `option` IS NULL THEN 'MCQ' ELSE `option` END as `option` 
FROM question_table

edited May 23, 2017 at 12:14

CommunityBot

11 silver badge

answered Jun 16, 2015 at 14:19

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

3 Comments

lakshmen Over a year ago

tried this as well. it just returned me the column of options with 'MCQ'

lakshmen Over a year ago

I wanted it to replace it.

Tim Schmelter Over a year ago

@lakesh: if you want to update your table use UPDATE... of course.

axiac · Accepted Answer · 2015-06-16 14:24:50Z

0

'NULL' is a string, NULL is a special value that means "unknown/unavailable". They are totally different things.

MySQL function IFNULL() handles NULL values (they cannot be compared using the regular comparison operators). You can use the regular comparison operators (=, <>) to work with strings, even when they are 'NULL'.

If your query produces 'NULL' values in the result set it means the values in the database are not NULL but strings ('NULL').

In this case the query you need is:

SELECT IF(`option` = 'NULL', 'MCQ', `option`) AS `option` 
FROM question_table

answered Jun 16, 2015 at 14:24

axiac

73.4k11 gold badges120 silver badges150 bronze badges

Collectives™ on Stack Overflow

How to replace a column with null values in mysql

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related