Delete statements as where clause in SQL Server-CodePudding

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.

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.

CodePudding user response：

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.