Results 1 to 2 of 2
2004-02-11, 08:21 #1
- Join Date
- Feb 2002
- Thanked 0 Times in 0 Posts
deleteondition all with where (Access 2000)
I want to delete all the records from the tables orders and order details,
where the customerid = 100
I cannot do it because Access says it cannot delete all the records.
the three tables are connected like that customers-orders-order details
The table orders contains the cutsomerid as a foreign key.
How can i do it ?
2004-02-11, 08:36 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: deleteondition all with where (Access 2000)
You have two options:
1) Select Tools | Relationships... Double click the join between Orders and Order Details (on the OrderID field), and make sure that all the check boxes are ticked:
- Enforce Relational Integrity
- Cascade Update Related Fields
- Cascade Delete Related Records
Click OK to close the Edit Relationships dialog, then close the Relationships window. If you now try to delete the records from Orders with CustomerID = 100, Access will delete related records in Order Details too, after asking for a confirmation.
2) Another way is to delete the records in the Order Details table first. The SQL for a delete query to do this is
DELETE * FROM [Order Details] WHERE OrderID In (SELECT OrderID FROM Orders WHERE CustomerID=100)
Only then delete the records from Orders:
DELETE * FROM Orders WHERE CustomerID=100