CODEWITHSUNDEEP
phpmysqlnull
Shafizadeh
Shafizadeh

Reputation: 10340

How to change all empty rows to NULL?

I have a table like this:

// mytable
+----+--------+
| id |  name  |
+----+--------+
| 1  |  Jack  |
| 2  |        |
| 3  |  Peter |
| 4  |        |
| 5  |        |
| 6  |  Alvin |
+----+--------+

Also the output of this query is always 1: 

SELECT ... (mytable.name IS NOT NULL) as col ... FROM ...

//=> echo result["col"]; -> always it will be "1"

Now, how can I change empty records to NULL? Because I want it returns 1 just for where there is a name and if there is not any name I want to it returns 0.

Actually I want something like this:

// newtable
+----+--------+
| id |  name  |
+----+--------+
| 1  |  Jack  |
| 2  |  Null  |
| 3  |  Peter |
| 4  |  Null  |
| 5  |  Null  |
| 6  |  Alvin |
+----+--------+

How can I convert my table to this ^ ?

Upvotes: 2

Views: 463

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

If your table column name is nullable you can update your data:

UPDATE mytable
SET name = NULL
WHERE name = '';

If your column name is NOT NULL you need to alter it first to allow NULL.

or if you need to create new table you can use:

CREATE TABLE newtable
AS
SELECT id, CASE WHEN TRIM(name) = '' THEN NULL ELSE name END AS name
FROM mytable;

Upvotes: 4

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

If your table column name is nullable you can update your data and name column should having space 

UPDATE mytable
SET name = NULL
WHERE replace(name,' ','') = '';

Upvotes: 1

Related Questions