Please Help with Simple Delete Query Problem

  • Thread starter Thread starter robbie
  • Start date Start date
R

robbie

I have two tables, and want to delete records from one table (table A)
if the date is found in the second table (table B). Here's the table
structures:

Table A:
ID [autonumber]
Date [date]
Sales [number]

Table B:
ID [autonumber]
Date [date]

I want to delete all records from table A where the date is in table B.
What I thought would work was:

DELETE a.*
FROM a INNER JOIN b ON a.date=b.date;

But, this isn't working. I get a "Could not delete from specified
tables" error.

I've also tried using the UniqueRecords option to Yes, but still get
same error.

Any help greatly appreciated.
 
Interesting. What you posted should work.

DELETE DistinctRow A.*
FROM A INNER JOIN B
ON A.[Date] = B.[Date]

I would check and make sure you don't have
DELETE *
FROM ...

Or
DELETE A.*, *
FROM

An alternative query would be

DELETE DistinctRow A.[Date]
FROM A
WHERE A.[Date] in
(SELECT B.[Date]
FROM B)
 
Try this.

DELETE [Table A].Date
FROM [Table A] INNER JOIN [Table B] ON [Table A].Date = [Table B].Date;
 
I couldn't get it to work until doing the IN with the subselect. Very
strange... I couldn't get it to work doing an INNER JOIN even with the
DISTINCTROW...

thx, all
 
Back
Top