CODEWITHSUNDEEP
sql
user3107674
user3107674

Reputation: 27

How to remove an entire row if one column is empty

Say I have the following table:

Tree     Park      Slide       
1           1        1
1           1        1
                     1
1                    1

What kind of code would I use to remove the rows that have empty columns such that I would just have the following result 

 Tree     Park      Slide       
    1           1        1
    1           1        1

I am new to sql and was wondering on some tips on how to write this code. Would I use a case statement such that

Case
WHEN Tree IS NULL OR Park IS NULL
--Then what would I say to remove the row

Upvotes: 2

Views: 621

Answers (6)

Sahil Sareen
Sahil Sareen

Reputation: 1834

DELETE FROM dbo.TableName
WHERE (Tree+Park) IS NULL

This is because

NULL+NonNullValue = NULL


and

NULL+NULL=NULL

(obviously)

Simpler way would be:

DELETE FROM TABLE
WHERE Tree is NULL OR Park is NULL

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460340

Use DELETE to delete a row and check it with IS NULL in the WHERE clause. 

DELETE FROM dbo.TableName
WHERE Tree IS NULL OR Park IS NULL OR Slide IS NULL

If you also want to delete rows where one of these columns is not null but empty text(if it's a varchar column):

DELETE FROM dbo.TableName
WHERE (Tree IS NULL OR Tree = '') 
 OR   (Park IS NULL OR Park = '') 
 OR   (Slide IS NULL OR Slide = '')

Upvotes: 7

Rahul Tripathi
Rahul Tripathi

Reputation: 172628

You may try using a delete like this:-

DELETE FROM tablename
WHERE Tree IS NULL 
   OR Park IS NULL
   OR Slide IS NULL

Upvotes: 0

Palpatim
Palpatim

Reputation: 9272

Yes, something like DELETE FROM TABLE WHERE Tree IS NULL or Park IS NULL or Slide IS NULL will work.

Upvotes: 0

D Stanley
D Stanley

Reputation: 152644

If you mean physically deleting then just put that in your WHERE:

DELETE FROM {table}
WHERE Tree IS NULL 
   OR Park IS NULL
   OR Slide IS NULL

Or take advantage of the fact that {null} + n = {null} and just do:

DELETE FROM {table}
WHERE (Tree+Park+Slide) IS NULL

But I would definitely add a comment to explain why that works.

If you mean excluding from SELECT results then you could use:

SELECT *
FROM {table}
WHERE Tree IS NOT NULL 
  AND Park IS NOT NULL
  AND Slide IS NOT NULL

Upvotes: 2

Paddy
Paddy

Reputation: 33867

No case statement required, just a where clause:

SELECT *
FROM Table
WHERE Tree IS NOT NULL
AND Park IS NOT NULL
AND Slide IS NOT NULL

Note - reading the comments - this does not delete from the DB, just filters the output of a select. Tim Schmelter's answer would be correct for deletion.

Upvotes: 0

Related Questions