delete rows using except ?

Found out that except is very fast, but is it possible to delete rows using except ?

Like:

delete accountnumber, central
from table1
except
select accountnumber, central
from table 2

Jam:

The DELETE FROM ... WHERE NOT EXISTS is likely to be faster than a DELETE FROM ... FROM ... EXCEPT; something such as:

delete from table1
from table1 a
where not exists
( select 0 /* a dummy */ from table2 b
where a.accountNumber = b.accountNumber
and a.central = b.central
)

Dave

|||

Hmmmm. This query seems to have the same amount of logical IO as the previous query:

delete from table1
from table1 a
inner join
( select accountNumber,
central
from table1
except
select accountNumber,
central
from table2
) b
on a.accountNumber = b.accountNumber
and a.central = b.central

I'll do a little more digging. At the moment, this query also looks viable.

Dave

|||

Sorry about that, I was simultaneously dealing with an implementation problem. Really, I cannot go any farther with my mock-up without knowing specifics about your actual implementation because your implementation will have a big impact on the execution plan. Yeah, I tested out with the same amount of logical IOs with both queries but this really is more qualitative than anything and doesn't prove too much.

I can say little more than the qualitative viability of the EXCEPT query; however, I don't consider this a simpler query than the NOT EXISTS query. Can you give more information about your two tables -- especially index information?

Dave