Please Help with Simple Delete Query Problem

[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.

 

[John Spencer]

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)

 

[Guest]

Try this.

DELETE [Table A].Date
FROM [Table A] INNER JOIN [Table B] ON [Table A].Date = [Table B].Date;

 

[robbie]

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