Removing all the rows in a table where a subset of columns have all 0s/NULL-CodePudding

I have a table with 30 columns. I want to remove all the rows in the table where a subset of columns have all the values in the row as either 0 or NULL. I know that I can write some query as

DELETE 
FROM Mytable
Where (col1 is NULL OR col1 = 0  ) AND
(col3 is NULL OR col1 = 0  ) AND
(col5 is NULL OR col1 = 0  )
.
.
.
many such lines.

Example Table (with smaller no of columns):

   |  col1 | col2   | col3  | col4  | col5   | col6 |
   |:---- |:------:| -----:||:---- |:------:| -----:|
R1 | 1    | 2      | 3     | 1     | 2      | 3     |
R2 | 1    |        | 0     |       | 0      |       |
R3 | 1    |        | 0     |       | 0      |       |
R4 | 1    | 2      | 3     | 1     | 2      | 3     |
R5 | 1    | 2      | 3     | 1     | 2      | 3     |

Let the subset of columns be {col2,col3,col4,col5,col6}.(This is just an example, there may be many such columns) . So the output of the query should be only the rows R1,R4 and R5 as R2,R3 contain either 0 or Null in the columns col2..6.

Output:

   |  col1 | col2   | col3  | col4  | col5   | col6  |
   |:---- |:------:| -----:||:---- |:------:| -----:|
R1 | 1    | 2      | 3     | 1     | 2      | 3     |
R4 | 1    | 2      | 3     | 1     | 2      | 3     |
R5 | 1    | 2      | 3     | 1     | 2      | 3     |

But I feel that the query I wrote isn't a scalable way of writing this. Can anyone help me with this?

CodePudding user response：

You could check if the sum of all the columns in question equals 0. The use of ifnull is to turn nulls into 0 to allow the arithmetic operation

where ifnull(col1,0)   ifnull(col2,0)   ifnull(col3,0) .... = 0

CodePudding user response：

I think you need to use a HAVING.

The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.