CODEWITHSUNDEEP
mysqlsql
user197324
user197324

Reputation: 47

How to replace values present in multiple columns?

I have a database table foo and I want to replace null values present in specific columns with empty string. For removing null values present in particular column x, I would do like 

update foo set x = '' where x is null;

Is there anyway I would do like,

update foo set x,y,z = '' where x,y,z is null;

Upvotes: 1

Views: 2037

Answers (4)

kaarel
kaarel

Reputation: 617

This will update all rows, where any of columns x, y or z has NULL value and uses COALESCE function to modify values only in columns where value is NULL:

UPDATE
    foo
SET
    x = COALESCE(x, ''),
    y = COALESCE(y, ''),
    z = COALESCE(z, '')
WHERE
    NULL IN (x, y, z)

Upvotes: 1

Abbas
Abbas

Reputation: 412

you should try this

UPDATE foo SET x ="", y = "", z = "" WHERE x is NULL and y is NULL and z is NULL

Upvotes: 0

Germanaz0
Germanaz0

Reputation: 914

Is simpler to do this:

UPDATE foo SET x='', y='', z='' WHERE x IS NULL AND y IS NULL AND z IS NULL

Or this one if you want to edit only the values that are NULL

UPDATE foo SET x = IF(x IS NULL, '', x), y = IF(y IS NULL, '', y) ...

Upvotes: 0

Alex K.
Alex K.

Reputation: 175956

You can:

set 
   x = case when x is null then '' else x,
   y = case when y is null then '' else y,
   z = case when z is null then '' else z
   ...

Upvotes: 3

Related Questions