Delete statements as where clause in SQL server

February 2, 2022

So I have a bunch of delete statements that happen on a temp table in SQL Server, the temp table is created based on SQL. While I am trying not to use a temp table, I would like to get the delete statements as filters in the where, so that I can exclude them, instead of creating a temp table and deleting from them.

So the delete statements looks like this

delete from temptableA where ispending = 'F' and isviewable = '0';
delete from temptableA where ispending = 'T' and iscomparable = '0';
delete from temptableA where ispending = 'T' and iscomparable is null and isveiwable = '0';
delete from temptableA where ispending is null and iscomparable ='0';

SQL to create the temptableA

select ispending, isviewable, iscomparable, ID
from tableA
where name = 'Karen'

How would I exclude those in the SQL I have. I tried OR statements and they don’t work as expected.

>Solution :

The first step would be to replace all the delete statements by one by combining the conditions with OR

delete from temptableA
where
    ispending = 'F' and isviewable = '0' OR
    ispending = 'T' and iscomparable = '0' OR
    ispending = 'T' and iscomparable is null and isveiwable = '0' OR
    ispending is null and iscomparable ='0'

Note that AND has a higher precedence than OR. Just like multiplication has a higher precedence than addition. Otherwise we would have to add parentheses around the four conditions.

Now, we have a single WHERE clause and can invert it to keep the desired rows:

SELECT ispending,isviewable,iscomparable,ID
from tableA
where
    name = 'Karen' AND
    NOT (
        ispending = 'F' and isviewable = '0' OR
        ispending = 'T' and iscomparable = '0' OR
        ispending = 'T' and iscomparable is null and isveiwable = '0' OR
        ispending is null and iscomparable ='0'
    )

We could use De Morgan’s laws to simplify this a bit, but its not worth the candle.