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?
2
Answers
You could check if the sum of all the columns in question equals 0. The use of
ifnull
is to turnnulls
into0
to allow the arithmetic operationI think you need to use a
HAVING
.The
HAVING
clause was added to SQL because theWHERE
keyword cannot be used with aggregate functions.