11

I have a dataset with over 100,000 rows, over 100 columns and where some values are NULL. Now I want to remove all the rows which contain NULL values.

Can anybody suggest the sql command for it?

Improve this question
1
  • 3
    Please post the code you have written so far. People generally do not like to just write your code for you. As it is, this is a work description, not a question. Commented Apr 7, 2011 at 23:57

2 Answers 2

Reset to default
16

With the little information you've provided:

DELETE FROM table WHERE colA IS NULL OR colB is NULL

Add further conditions for each column that you want to check.

Change OR to AND if you only want to delete rows where all of the columns are NULL.

It's fairly easy to generate the SQL for this using a query on user_tab_columns if you don't want to type it out by hand.

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

Comments

0

use a scripting language like PHP to retreive all column names and then construct your SQL query. Using pure SQL could get tricky.

Improve this answer

3 Comments

DB2, Oracle, SQL Server, PostgreSQL, MySQL, Interbase, Paradox, MS Access and even dBase have easier ways to get all column names from a table, without having to write any kind of script for that.
Thanks Dave,ITroubs & rsenna! I just want to know if we there's a posibility to create vba like script for deleting rows within SQL.
I don't knoow VBA but if it is possible in PHP then it should be possible in VBA too.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.