replace 0 with null in mysql

Question

I want to replace 0's in mysql table with 'NULL'. I have read that querying the following way would replace 'NULL' with 0

SELECT COALESCE(null_column, 0) AS null_column FROM whatever;

But how to the other way?

Solution 1

You can use NULLIF, which will return NULL if the value in the first parameter matches the value in the second parameter.

SELECT NULLIF(null_column, 0) AS null_column FROM whatever

Solution 2

update `whatever` set `null_column` = null where null_column = 0;

Solution 3

Just use an UPDATE query, it's way faster: UPDATE table SET value=NULL WHERE value=0.

Solution 4

I used

UPDATE userDetails set fame=0 where fame IS NULL;

if it to work. Since = did not work for me.

View more solutions

Comments

[LittleBobbyTables - Au Revoir]

'' is not the same as NULL

[Vlad Alivanov]

Not working for me in case of decimals and strict mode