Delete query

[husain]

Hello people! This is my first post here. I am a beginner in Access and I hope I can find the solution to my problem here.

I have two tables with a one to many relationship. The 'parent' table is called Orders and the related table is called OrderDetails. I have created a query that will list out all OrderID's that do not have corresponding records in the OrderDetails table.

What I want to do is delete all records from the Orders table that do not have corresponding records in the OrderDetails table so I converted this select query into a delete query. However, when I run it, I get this error: "Could not delete from specified tables."

The SQL for the select query:

SELECT Orders.OrderID
FROM Orders LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
WHERE (((OrderDetails.OrderID) Is Null));

SQL when I convert the above to a delete query:

DELETE Orders.OrderID, OrderDetails.OrderID
FROM Orders LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
WHERE (((OrderDetails.OrderID) Is Null));

Could someone please point out where I have made a mistake? Thanks

 

[Twin]

Try the following:
DELETE Orders.*, OrderDetails.OrderID
FROM OrderDetails RIGHT JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
WHERE (((OrderDetails.OrderID) Is Null));
Prior to this set the Primary Key in both tables to OrderID field.
It worked on my test db.

HTH

Chris

 

[The_Doc_Man]

Or you could try this:

DELETE * FROM [Orders] where DCount( "[OrderDetail]![OrderId]", "OrderDetail", "[OrderDetail]![OrderID] = " & Cstr([Orders]![OrderID] ) :

That way you aren't working from a JOIN, so shouldn't have trouble with INNER JOIN, LEFT OUTER JOIN, etc.

 

[husain]

Try the following:
DELETE Orders.*, OrderDetails.OrderID
FROM OrderDetails RIGHT JOIN Orders ON OrderDetails.OrderID = Orders.OrderID
WHERE (((OrderDetails.OrderID) Is Null));
Prior to this set the Primary Key in both tables to OrderID field.

I tried that but it did not work. It gave me the same error: "Could not delete from specified tables."

As for the primary keys, since it is a one to many relationships I have a composite primary key in the OrderDetails table. I think this should not be a problem. Should it?

 

[husain]

Or you could try this:

DELETE * FROM [Orders] where DCount( "[OrderDetail]![OrderId]", "OrderDetail", "[OrderDetail]![OrderID] = " & Cstr([Orders]![OrderID] ) :

That way you aren't working from a JOIN, so shouldn't have trouble with INNER JOIN, LEFT OUTER JOIN, etc.

This gave me an error:

"Syntax error (missing operator) in query expression 'DCount( "[OrderDetail]![OrderId]", "OrderDetail", "[OrderDetail]![OrderID] = " & Cstr([Orders]![OrderID] ) :'.

 

[RV]

A DELETE statement is used to remove complete rows from a (= ONE) table.

Standard statement is:

DELETE * FROM Table;

So, in a DELETE statement you can't refer to more then one table in your FROM component.

Use this:

SELECT *
FROM Orders
WHERE Orders.OrderID NOT IN
(SELECT OrderDetails.OrderID
FROM OrderDetails);

RV

 

[husain]

Worked like a charm (changed the SELECT to DELETE)! Thank you people so much.