Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 768
Kathy Avatar asked Feb 10 '11 21:02

Kathy


2 Answers

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)
like image 98
jon3laze Avatar answered May 21 '23 08:05

jon3laze


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)
like image 44
Christoph Avatar answered May 21 '23 09:05

Christoph