delete from table where not in other table

  • Thread starter Thread starter Andrew Brill
  • Start date Start date
A

Andrew Brill

Does anyone know a (much!) faster way to write the following please?

DELETE * FROM TRANSHEADER2 WHERE HEADERID NOT IN
(SELECT HEADERID FROM TRANSHEADER)


An unmatched query (below) runs a lot quicker (as a SELECT) but I can't work
out how to turn an unmatched query into a delete query. It complains:
'must use an updatable query':

DELETE TRANSHEADER2.*
FROM TRANSHEADER2 LEFT JOIN TRANSHEADER ON TRANSHEADER2.HEADERID =
TRANSHEADER.HEADERID
WHERE (((TRANSHEADER.HEADERID) Is Null));



Thanks!
Andrew
 
Had an idea. Rather than deleting the ones I don't want, I select the ones I
do want (by simply joining table 1 and 2) as an append query into a 3rd
table
 
Thanks, that works great! Only takes a couple of seconds to run. I don't
know how long my original one took - I got fed up after 10 minutes(!) and
cancelled.
 
Back
Top