mySQL Query Remove Null Records in Column

Question

I have a large mySQL database and I want to remove every record that is empty, not null in a certain column. What is the best way to do write a SQL query for this?

Currently I have tried:

 DELETE FROM Businesses WHERE WEBADDRESS IS NULL

But it did not delete anything. There are 44,000 records and almost 80% of them are null in that column.

Fixed your question based on your comment. This is a much better SO question now, providing some information on what you have done and more context for people trying to help. — Matthew
– Matthew, Commented Sep 23, 2013 at 15:25

Matthew · Accepted Answer · 2013-09-23 15:28:16Z

22

DELETE FROM myTable WHERE myColumn IS NULL

Link to MySQL page for DELETE syntax: http://dev.mysql.com/doc/refman/5.7/en/delete.html

IF the column is not NULL but just blank you would need to do something like:

DELETE FROM myTable WHERE myColumn = ''

Based on the information you also provided in the comments, the values are likely being loaded as empty ('') and not NULL: http://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html

The second query should work.

edited Sep 23, 2013 at 15:28

answered Sep 23, 2013 at 15:02

Matthew

9,9584 gold badges50 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Michael Falciglia Over a year ago

I tried it, but it did not delete anything. There are 44,000 records and almost 80% of them are null in that column. Here is my query DELETE FROM Businesses WHERE WEBADDRESS IS NULL

Matthew Over a year ago

Do you have a foreign key dependency on these records? Is there any error message? Is the column actually null or just blank?

Michael Falciglia Over a year ago

I had a CSV file that has 19 columns, so I created a table and named each column like the CSV had them and made them all VARCHAR 255, then imported the CSV. The import was successful with no errors and I made no other changes to the table structure.

Michael Falciglia Over a year ago

oh I see, null is different than being blank. I did not know that. I will try that now.

Matthew Over a year ago

@OP the second query should work, MySQL should be importing these empty columns as '' unless you are specifying \N for the rows which should be null.

juergen d · Accepted Answer · 2013-09-23 15:02:47Z

2

delete from your_table
where certain_column is null

answered Sep 23, 2013 at 15:02

juergen d

205k40 gold badges305 silver badges377 bronze badges

Comments

hcarver · Accepted Answer · 2013-09-23 15:05:01Z

1

DELETE from `<tablename>`
WHERE `<columnname>` is null

edited Sep 23, 2013 at 15:05

hcarver

7,3445 gold badges44 silver badges67 bronze badges

answered Sep 23, 2013 at 15:04

Purple

1041 silver badge13 bronze badges

Comments

php · Accepted Answer · 2014-01-04 07:06:01Z

1

delete from table_name where column=''

answered Jan 4, 2014 at 7:06

php

4,4551 gold badge27 silver badges13 bronze badges

Comments

Eric Aya · Accepted Answer · 2018-10-24 13:11:06Z

0

Don't worry about count of data. Mysql can handle it. Just write:

 DELETE FROM `Businesses` WHERE `WEBADDRESS` = '';

edited Oct 24, 2018 at 13:11

Eric Aya

70.2k36 gold badges190 silver badges266 bronze badges

answered Oct 24, 2018 at 13:04

Nevermore

1,7538 gold badges33 silver badges60 bronze badges

Comments

Ahsan Raza · Accepted Answer · 2018-02-17 06:45:25Z

-1

delete from table_name where column_name is null; this query will definitely work for you.

answered Feb 17, 2018 at 6:45

Ahsan Raza

5235 silver badges11 bronze badges

Collectives™ on Stack Overflow

mySQL Query Remove Null Records in Column

6 Answers 6

5 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related