SQL server - delete rows where multiple columns have either null or zero value

Question

I'm a DB newbie, and am struggling with this. I have an existing table with the following columns:

Showroom, Salesperson, Time1, Time2, Time3, Dte

I'm trying to delete all rows within the table that have either null or zero values in all 3 time columns. I tried the following:

DELETE FROM myTable 
WHERE EXISTS(
              SELECT * 
              FROM myTable 
              WHERE (Time1 IS NULL OR Time1 = 0) 
                AND (Time2 IS NULL OR Time2 = 0) 
                AND (Time3 IS NULL OR Time3 = 0)
            )

Thankfully I'm working on a test version of the database, as I wiped out all of the data. Any help would truly be appreciated.

See any of the answers below for the right approach. The reason what you wrote deleted all the data is that Exists is true whenever the statement within the exists returns any records. — Christoph
– Christoph, Commented Feb 10, 2011 at 21:36

jon3laze · Accepted Answer · 2011-02-10 21:44:08Z

23

The query should be formatted like this:

DELETE 
FROM myTable 
WHERE (Time1 IS NULL OR Time1 = 0) 
AND (Time2 IS NULL OR Time2 = 0) 
AND (Time3 IS NULL OR Time3 = 0)

When doing DELETE statements ~~I think~~ it is always best to first create your SELECT statement, and then change it.

SELECT * --If this returns the correct records, simply change to DELETE
FROM myTable 
WHERE (Time1 IS NULL OR Time1 = 0) 
AND (Time2 IS NULL OR Time2 = 0) 
AND (Time3 IS NULL OR Time3 = 0)

edited Feb 10, 2011 at 21:44

answered Feb 10, 2011 at 21:35

jon3laze

3,2066 gold badges42 silver badges69 bronze badges

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

3 Comments

HLGEM Over a year ago

Abosolutely, never do a delete without running a select first to see what records will be affected, and putting it in a transaction without running the commit until you see how many records were affected is also helpful when running ad hoc delete queries.

Kathy Over a year ago

Thank you! That's a great tip and I will definitely do that from now on.

Ronnis Over a year ago

+1 for mentioning the "start with select first". A few too many bloopers on MyISAM tables (no rollback here) taught me to start with a select first, or at least start with the WHERE clause first, and then type in the UPDATE/DELETE table_name :)

Christoph · Accepted Answer · 2011-02-10 21:32:20Z

4

What you want is just;

DELETE myTable
WHERE
  (Time1 IS NULL OR Time1 = 0)
  AND (Time2 IS NULL OR Time2 = 0)
  AND (Time3 IS NULL OR Time3 = 0)

answered Feb 10, 2011 at 21:32

Christoph

4,4013 gold badges26 silver badges38 bronze badges

1 Comment

Kathy Over a year ago

Thank you for your help, and for replying so soon.

Oded · Accepted Answer · 2011-02-10 21:32:39Z

1

The EXISTS is superfluous (as is the FROM - it isn't needed for DELETEs):

DELETE myTable 
WHERE ((Time1 IS NULL OR Time1 = 0) 
  AND (Time2 IS NULL OR Time2 = 0) 
  AND (Time3 IS NULL OR Time3 = 0))

answered Feb 10, 2011 at 21:32

Oded

501k102 gold badges900 silver badges1k bronze badges

1 Comment

Kathy Over a year ago

Thank you for the prompt answer!

nybbler · Accepted Answer · 2011-02-10 21:33:18Z

1

Try this instead:

DELETE 
FROM myTable 
WHERE 
  (Time1 IS NULL OR Time1 = 0) AND 
  (Time2 IS NULL OR Time2 = 0) AND 
  (Time3 IS NULL OR Time3 = 0)

The reason all of your records are being deleted is because the EXISTS result is true as long as there exists a single record that has NULL or 0 for all three columns. Since your WHERE clause of the delete statement doesn't identify which records to delete, it is essentially the same as DELETE FROM myTable WHERE 1=1

answered Feb 10, 2011 at 21:33

nybbler

4,87132 silver badges23 bronze badges

Comments

rayudu · Accepted Answer · 2020-09-06 17:49:58Z

1

Here is the query to delete blank rows as well as NULL

DELETE FROM table_name WHERE COL_NAME='' OR COL_NAME IS NULL;

answered Sep 6, 2020 at 17:49

rayudu

1111 gold badge1 silver badge7 bronze badges

Comments

Piotr Olaszewski · Accepted Answer · 2015-09-22 06:06:02Z

0

DELETE myTable WHERE 
(ISNULL(Time1,0) = 0) AND (ISNULL(Time2,0) = 0) AND (ISNULL(Time3,0) = 0)

edited Sep 22, 2015 at 6:06

Piotr Olaszewski

6,2485 gold badges40 silver badges67 bronze badges

answered Sep 22, 2015 at 6:03

Srikanth CHindam

1011 silver badge5 bronze badges

Comments

fcdt · Accepted Answer · 2020-10-08 09:29:16Z

0

What you can do is to create a select query in MS automatically, then find and replace symbols [ with (ISNULL([, ] with ],0) = 0). and to get AND condition for all numeric fields (ISNULL(Time1,0) = 0) AND.

edited Oct 8, 2020 at 9:29

fcdt

2,5215 gold badges17 silver badges27 bronze badges

answered Oct 8, 2020 at 8:11

PLANSIS

12 bronze badges

Collectives™ on Stack Overflow

SQL server - delete rows where multiple columns have either null or zero value

7 Answers 7

3 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

3 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related