CODEWITHSUNDEEP
sql
Beta
Beta

Reputation: 1746

Delete rows conditionally

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?

Upvotes: 11

Views: 31261

Answers (2)

Dave Costa
Dave Costa

Reputation: 48111

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.

Upvotes: 16

ITroubs
ITroubs

Reputation: 11215

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

Upvotes: 0

Related Questions