Kristen Jones
asked on
Delete Records from Multiple tables at once
I have three tables in a MS SQL 2000 server:
1. [Orders]
2. [OrderList]
3. [OrderOptions]
There are over 70,000 Orders, and for each order there is between 1 to 10 items (records) in the OrderList table and for Every OrderList Record, there is anywhere from 1 to 20 records in the OrderOptions table. They are all interconnected, however there is now close to a Million records in the OrderOptions table as a result.
[Orders] has a primary key field called OrderId there is only one unique record with that orderId
[OrderList] has a OrderListID that is a primary key, but also has the OrderId field, however it may contain several records with the same OrderId. The two are linked and act as Items ordered tracked by the OrderID
[OrderOptions] has the OrderOptionID that is its primary key, but also has the OrderListID which also can have several of the same OrderListId's but there is NO reference to the OrderId This table hold the details about items in the OrderList table.
They are all interconnected but since the OrderOptions table does no have the OrderID it has to have a one to many Join from the OrderList table.
All I want to be able to do is create a Query, that will delete Old Orders but also remove the old data from the Other two tables. So for example, I want to delete all Orders where the OrderID is < 76750
1. [Orders]
2. [OrderList]
3. [OrderOptions]
There are over 70,000 Orders, and for each order there is between 1 to 10 items (records) in the OrderList table and for Every OrderList Record, there is anywhere from 1 to 20 records in the OrderOptions table. They are all interconnected, however there is now close to a Million records in the OrderOptions table as a result.
[Orders] has a primary key field called OrderId there is only one unique record with that orderId
[OrderList] has a OrderListID that is a primary key, but also has the OrderId field, however it may contain several records with the same OrderId. The two are linked and act as Items ordered tracked by the OrderID
[OrderOptions] has the OrderOptionID that is its primary key, but also has the OrderListID which also can have several of the same OrderListId's but there is NO reference to the OrderId This table hold the details about items in the OrderList table.
They are all interconnected but since the OrderOptions table does no have the OrderID it has to have a one to many Join from the OrderList table.
All I want to be able to do is create a Query, that will delete Old Orders but also remove the old data from the Other two tables. So for example, I want to delete all Orders where the OrderID is < 76750
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I get this error with that query:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'OrderOptions'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'OrderList'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'OrderOptions'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'OrderList'.
ASKER
Nevermind.... forgot to set the Default Database.... it's running now :) I'll report back
ASKER