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?
Add a comment
|
5 Answers
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
answered Sep 7, 2012 at 20:31
LittleBobbyTables - Au Revoir
33k25 gold badges113 silver badges115 bronze badges
Comments
update `whatever` set `null_column` = null where null_column = 0;
1 Comment
Vlad Alivanov
Not working for me in case of decimals and strict mode
Just use an UPDATE query, it's way faster: UPDATE table SET value=NULL WHERE value=0.
1 Comment
LittleBobbyTables - Au Revoir
'' is not the same as NULLI used
UPDATE userDetails set fame=0 where fame IS NULL;
if it to work. Since = did not work for me.
Comments
I have been using this method to clean individual columns but I would like to see if I can update an entire table doing the same method
UPDATE table SET value=NULL WHERE value=0.
Comments
