CODEWITHSUNDEEP
sqlsql-server
alangilbi
alangilbi

Reputation: 331

Delete statements as where clause in SQL Server

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.

Upvotes: 0

Views: 130

Answers (1)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112334

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. The parentheses around the four conditions are not required but enhance clarity.

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.

Upvotes: 5

Related Questions