delete join

  • Thread starter Thread starter dale
  • Start date Start date
D

dale

I am trying to get the following query to work and it says that it can
not find the table to delete from. Any ideas?

delete ebentryscore.*
FROM ebentryscore LEFT JOIN EbentryClassJudges ON
(EbentryClassJudges.EntryBlankID = ebentryscore.entryblankid) AND
(EbentryClassJudges.HorseID = ebentryscore.horseid) AND
(EbentryClassJudges.ClassID = ebentryscore.classid) AND
(EbentryClassJudges.JudgeID = ebentryscore.judgeid)
WHERE ebentryclassjudges.judgeid is null;
 
I am trying to get the following query to work and it says that it can
not find the table to delete from. Any ideas?

delete ebentryscore.*
FROM ebentryscore LEFT JOIN EbentryClassJudges ON
(EbentryClassJudges.EntryBlankID = ebentryscore.entryblankid) AND
(EbentryClassJudges.HorseID = ebentryscore.horseid) AND
(EbentryClassJudges.ClassID = ebentryscore.classid) AND
(EbentryClassJudges.JudgeID = ebentryscore.judgeid)
WHERE ebentryclassjudges.judgeid is null;
Click to expand...

Odd; this looks OK. If you change it to a Select query do you see the
expected rows (those with no matching records in EbentryClassJudges)?

John W. Vinson[MVP]
 
John,

Yes this will work as a select query. The ebentryclassjudges is a query
not a table and I am using access xp.

Dale
 
John,

Yes this will work as a select query. The ebentryclassjudges is a query
not a table and I am using access xp.
Click to expand...

Very odd!

Try a Subquery instead:

delete ebentryscore.*
FROM ebentryscore
WHERE NOT EXISTS
(SELECT JudgeID FROM EbentryClassJudges
WHERE
(EbentryClassJudges.EntryBlankID = ebentryscore.entryblankid) AND
(EbentryClassJudges.HorseID = ebentryscore.horseid) AND
(EbentryClassJudges.ClassID = ebentryscore.classid) AND
(EbentryClassJudges.JudgeID = ebentryscore.judgeid));


John W. Vinson[MVP]
 
John,

I had it as a subquery before and it worked. I am trying to speed it up.
Using the subquery is to slow.

Dale
 
Back
Top