Possible to use INNER JOIN in a DELETE?

[jal]

A sql server article did the following:

DELETE FROM Orders
FROM Customers c INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE Country = 'USA'

Does Access likewise permit the INNER JOIN keyword in a DELETE query? If so, can someone provide an example please?

 

[mearle]

DELETE Orders.*
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Country = 'USA'

 

[jal]

DELETE Orders.*
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Country = 'USA'

Ah, thank you very much. I didn't see this in the Access 2003 documentation (maybe I missed it) and had concluded it wasn't possible. I've added your example to my notefile.

 

[jal]

DELETE Orders.*
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
WHERE Country = 'USA'

Well, I'm going to have to conclude that the above syntax is unreliable - unless you can explain why I am getting the error "Could not delete from specified tables".

Others who got the same error seem to have found themselves compelled to use a subquery instead of an inner join.

The compiler accepts the query but throws the error at runtime.

 

[mearle]

Is CustomerID the primary key in the Customers table?
If not, you will get this error.

 

[jal]

Is CustomerID the primary key in the Customers table?
If not, you will get this error.

My apologies. I didn't realize that was a requirement.

Unfortunately, in my situation the fields on which I am joining will not be the primary key. So I guess I can't use this method.

 

[georgedwilkinson]

Alternative:

Delete from Orders
where Orders.CustomerID in
(select Customers.CustomerID from Customers where Customers.Country = 'USA');