combining delete query

is there a way where i can combine all of these into one query. I have multiple delete query and would rather have just one.


Delete from x where challenger = bob AND challengee != smith
Delete from x where challenger = smith
Delete from x where challenger = bob AND challengee != smith
Delete from x where challengee = bob

you can try using a temp table for example

your #temp table contains following data
challenger challengee
bob smith
smith blank
bob blank

delete x from x inner join #temp on x.challenger = #temp.challenger and
x.challengee != #temp.challengee

or

delete x from x inner join #temp on x.challenger = #temp.challenger and
x.challengee <> #temp.challengee


delete x from x inner join #temp on x.challenger = #temp.challenger

Delete from x
where ( challenger = bob AND challengee != smith )
or ( challenger = smith )
or ( challenger = bob AND challengee != smith )
or ( challengee = bob )

But your 1st and 3rd conditions are identical. There's not need to duplicate. So

Delete from x
where ( challenger = bob AND challengee != smith )
or ( challenger = smith )
or ( challengee = bob )

Which means we can then further reduce that to

[code]
Delete from x
where ( challenger = bob AND challengee != smith )
or ( challenger IN (smith, bob ) )

Don't omit the parentheses or you will have a horrible mess.

Jassi's solution would work, but it seems like 150% overkill for something this simple.

Thank you Old Pedant!