CODEWITHSUNDEEP
mysqlsqlnull
ben
ben

Reputation: 341

replace 0 with null in mysql

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?

Upvotes: 19

Views: 64641

Answers (5)

REMY ARNAUD
REMY ARNAUD

Reputation: 1

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.

Upvotes: 0

Siddharth
Siddharth

Reputation: 9574

I used

UPDATE userDetails set fame=0 where fame IS NULL;

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

Upvotes: 3

Giulio Muscarello
Giulio Muscarello

Reputation: 1342

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

Upvotes: 8

Hawili
Hawili

Reputation: 1659

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

Upvotes: 15

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32680

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

Upvotes: 49

Related Questions