Solved

Delete Records from Multiple tables at once

Posted on 2011-09-08
5
259 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Jason Jones
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 36502954
Before you try anything, first backup your tables or database.

The following will do the trick:

delete oo
from OrderOptions oo
inner join OrderList ol on ol.OrderListID = oo.OrderListID
where ol.OrderID < 76750

delete OrderList 
where OrderID < 76750

delete Orders
where OrderID < 76750

Open in new window

0
 
LVL 1

Author Comment

by:Jason Jones
ID: 36503038
VERY Scary thought if I messed up..  I added the field OrderID to the OrderOptions table.  Can you give me an update query that would populate this new field, then I could have more control over deleting record :)
0
 
LVL 23

Assisted Solution

by:wdosanjos
wdosanjos earned 500 total points
ID: 36503094
The following query will populate the OrderOptions.OrderID column:

update oo
set OrderID = ol.OrderID
from OrderOptions oo
inner join OrderList ol on ol.OrderListID = oo.OrderListID

Open in new window


Something to keep in mind.  Adding OrderID to OrderOptions breaks the normalization.
0
 
LVL 1

Author Comment

by:Jason Jones
ID: 36503177
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'.
0
 
LVL 1

Author Comment

by:Jason Jones
ID: 36503190
Nevermind....  forgot to set the Default Database....  it's running now :)  I'll report back
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question