Writing a DELETE using a join

[Dave]

This works fine in T-SQL...

DELETE FROM orders
FROM Orders AS o INNER JOIN [order details] AS od ON rderID =od.OrderID
WHERE od.ProductID=2

Yet in Access, the following gives me the error message "Specify the table
containing the records you want to delete"....

DELETE o.orderid,*
FROM Orders AS o INNER JOIN [order details] AS od ON rderID = od.OrderID
WHERE od.ProductID=2;

How do I write a DELETE query in Access SQL using a JOIN?

 

[Ron Weiner]

Sohuldn't it be

DELETE o.*
FROM Orders AS o INNER JOIN [order details] AS od ON rderID = od.OrderID
WHERE od.ProductID=2

Just guessing it dosent like o.orderid, *

Ron W

 

[Ken Snell]

You need to use a subquery. The join causes problems for the delete query.

DELETE o.*
FROM Orders AS o
WHERE rderID IN
(SELECT od.OrderID
FROM [order details] AS od
WHERE od.ProductID=2);

 

[Dave]

Adding the DISTINCTROW to the DELETE clause as below will allow the query to
execute properly.

DELETE DISTINCTROW o.*, od.ProductID
FROM Orders AS o INNER JOIN [order details] AS od ON rderID = od.OrderID
WHERE (((od.ProductID)=1));

Thanks to Gary Walter for pointing out this detail.

You need to use a subquery. The join causes problems for the delete query.

DELETE o.*
FROM Orders AS o
WHERE rderID IN
(SELECT od.OrderID
FROM [order details] AS od
WHERE od.ProductID=2);


--

Ken Snell
<MS ACCESS MVP>

This works fine in T-SQL...

DELETE FROM orders
FROM Orders AS o INNER JOIN [order details] AS od ON rderID =od.OrderID
WHERE od.ProductID=2

Yet in Access, the following gives me the error message "Specify the table
containing the records you want to delete"....

DELETE o.orderid,*
FROM Orders AS o INNER JOIN [order details] AS od ON rderID = od.OrderID
WHERE od.ProductID=2;

How do I write a DELETE query in Access SQL using a JOIN?

 

[david epsom dot com dot au]

dunno about the 'DISTINCTROW', but I note that the problem
with the unqualified * has been fixed.

(david)

Adding the DISTINCTROW to the DELETE clause as below will allow the query to
execute properly.

DELETE DISTINCTROW o.*, od.ProductID
FROM Orders AS o INNER JOIN [order details] AS od ON rderID = od.OrderID
WHERE (((od.ProductID)=1));

Thanks to Gary Walter for pointing out this detail.

You need to use a subquery. The join causes problems for the delete query.

DELETE o.*
FROM Orders AS o
WHERE rderID IN
(SELECT od.OrderID
FROM [order details] AS od
WHERE od.ProductID=2);


--

Ken Snell
<MS ACCESS MVP>

This works fine in T-SQL...

DELETE FROM orders
FROM Orders AS o INNER JOIN [order details] AS od ON rderID =od.OrderID
WHERE od.ProductID=2

Yet in Access, the following gives me the error message "Specify the table
containing the records you want to delete"....

DELETE o.orderid,*
FROM Orders AS o INNER JOIN [order details] AS od ON rderID = od.OrderID
WHERE od.ProductID=2;

How do I write a DELETE query in Access SQL using a JOIN?