1

In my SQL tables there are rows where columnX has empty value (""). Now i want them i queried to select them and then delete them.

Query like:

tables has empty rows

Delete empty rows

How can i do this. Any idea

Anthony
  • 36,459
  • 25
  • 97
  • 163

4 Answers4

4

Depending on what exactly you mean by "empty" rows:

delete from yourTable where column1 is null

will delete where column1 has a null value. If you mean where multiple columns have nulls, it's just a matter of adding more conditions to the where clause:

delete from yourTable where column1 is null and column2 is null and column3 is null

If by empty you mean "has spaces in a text field or the field is empty" you can use some of the builtin functions to find them for example:

delete from yourTable where trim(column1)=''

which would find a row in the table where column1 only has white space in it and so on.

You might want to have a read of this article that I wrote on SQL, join and the like - it has got a fair bit in it about selecting the right rows from the table - and in your case, replace the select.... from where... with a delete from where...

Having said all that, I would really wonder why you are inserting data into your table that you don't want in it?

Community
  • 1
  • 1
Fluffeh
  • 33,228
  • 16
  • 67
  • 80
2

You can check each field for null or the empty string like this:

DELETE FROM table WHERE (column1 IS NULL OR column1 = '') AND (column2 IS NULL OR column2 = '')

Just add the rest of your columns to the WHERE clause.

Jonathon
  • 15,873
  • 11
  • 73
  • 92
1

Simple : delete from Test_table where c1 is null,....and cN is null

G.Nader
  • 847
  • 7
  • 9
-1

Ok Try this, i hope u'll find your solution

What you need is, first get empty rows

Select * From table_name Where column_name = "";

Then Delete the empty rows

Delete From table_name Where column_name = "";

or don't write the select query only write the delete query

I hope this solve your problem

deemi-D-nadeem
  • 2,343
  • 3
  • 30
  • 71