Delete based on another table

  • Thread starter Thread starter Song Su
  • Start date Start date
S

Song Su

I can SELECT but I cannot DELETE as follows.

DELETE Transcript.*, Transcript.SEM
FROM Transcript INNER JOIN YYYYFx ON Transcript.YYYY = YYYYFx.YYYY
WHERE (((Transcript.SEM)="3"));
 
Song Su said:
I can SELECT but I cannot DELETE as follows.

DELETE Transcript.*, Transcript.SEM
FROM Transcript INNER JOIN YYYYFx ON Transcript.YYYY = YYYYFx.YYYY
WHERE (((Transcript.SEM)="3"));
Click to expand...

In the query design right-click and bring up the query's properties (not the
properties of a field) and select Unique Records=Yes. Does that work?

Ed Metcalfe.
 
Try

DELETE *
FROM Transcript
WHERE SEM = "3"
AND YYYY IN (SELECT DISTINCT YYYY FROM YYYYFx)
 
I can SELECT but I cannot DELETE as follows.

DELETE Transcript.*, Transcript.SEM
FROM Transcript INNER JOIN YYYYFx ON Transcript.YYYY = YYYYFx.YYYY
WHERE (((Transcript.SEM)="3"));
Click to expand...

What is the error message? Does YYYY have a unique Index in either table? What
is the structure of the table YYYYFx (is that actually a table named 2007Fx
perhaps) and how - if at all - is it related to Transcript?

You might need to use a subquery instead:

DELETE Transcript.*
FROM Transcript
WHERE SEM = "3"
AND YYYY IN(SELECT [YYYY] FROM YYYYFx);

Back up your database first of course!!!


John W. Vinson [MVP]
 
have you tried:


DELETE DISTINCTROW Transcript.*
FROM Transcript INNER JOIN YYYYFx ON Transcript.YYYY = YYYYFx.YYYY
WHERE (((Transcript.SEM)="3"));



Vanderghast, Access MVP
 
Back
Top