...

View Full Version : combining delete query



adrtrama
12-08-2011, 09:16 AM
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

jassi.singh
12-08-2011, 10:28 AM
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

Old Pedant
12-08-2011, 07:24 PM
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.

adrtrama
12-09-2011, 01:44 AM
Thank you Old Pedant!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum