Solved

SQL Script to Delete records from multiple tables

Posted on 2011-09-30
10
294 Views
Last Modified: 2012-05-12
I started by writing a series of select statements just to make sure I had the correct tables and then I created a table(Customers_to_Delete) that contains all customers that need to be deleted from all of these other tables. My question is two-fold:
1) If I find any records for these customers in any table that begins with RM1 through RM2 do not delete any records. Otherwise delete all records in all other tables.
2) What is the most effective way to structure the SQL delete statements when you have a list of records in one table and then you have to delete corresponding records in a set of other tables based on some linked column? If you can just get me started in the right direction I will complete the remainder of the code.
-- Check the RM00101 table
select *
from [powmt]..[rm00101]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM00102 table
select *
from [powmt]..[rm00102]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM00103 table
select *
from [powmt]..[rm00103]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM00104 table
select *
from [powmt]..[rm00104]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM00105 table -- NOT NEEDED
select *
from [powmt]..[rm00105]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM00106 table
select *
from [powmt]..[rm00106]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM00201 table -- NOT NEEDED
select *
from [powmt]..[rm00201]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM00301 table - NOT NEEDED	
select *
from [powmt]..[rm00301]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM00302 table - NOT NEEDED
select *
from [powmt]..[rm00302]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM00303 table -- NOT NEEDED
select *
from [powmt]..[rm00303]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM00305 table -- NOT NEEDED
select *
from [powmt]..[rm00305]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM00401 table
select *
from [powmt]..[rm00401]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM00500 table -- NOT NEEDED
select *
from [powmt]..[rm00500]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM00700 table
select *
from [powmt]..[rm00700]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM10101 table
select *
from [powmt]..[rm10101]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM10201 table
select *
from [powmt]..[rm10201]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM10301 table
select *
from [powmt]..[rm10301]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM10501 table
select *
from [powmt]..[rm10501]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM10504 table
select *
from [powmt]..[rm10504]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM10601 table
select *
from [powmt]..[rm10601]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM10901 table  -- NOT NEEDED
select *
from [powmt]..[rm10901]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM20101 table
select *
from [powmt]..[rm20101]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM20102 table
select *
from [powmt]..[rm20102]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM20201 table
select *
from [powmt]..[rm20201]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM20400 table
select *
from [powmt]..[rm20400]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM20401 table
select *
from [powmt]..[rm20401]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM30101 table
select *
from [powmt]..[rm30101]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM30100O table
select *
from [powmt]..[rm30101O]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM30201 table
select *
from [powmt]..[rm30201]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM30202 table
select *
from [powmt]..[rm30202]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM30301 table
select *
from [powmt]..[rm30301]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM30401 table
select *
from [powmt]..[rm30401]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM305101 table
select *
from [powmt]..[rm30501]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM30502 table -- NOT NEEDED
select *
from [powmt]..[rm30502]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM30601 table 
select *
from [powmt]..[rm30601]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM30701 table 
select *
from [powmt]..[rm30701]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM30702 table 
select *
from [powmt]..[rm30702]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM40101 table -- NOT NEEDED
select *
from [powmt]..[rm40101]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM40102 table -- NOT NEEDED
select *
from [powmt]..[rm40102]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM40201 table -- NOT NEEDED
select *
from [powmt]..[rm40201]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM40401 table -- NOT NEEDED
select *
from [powmt]..[rm40401]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM40501 table -- NOT NEEDED
select *
from [powmt]..[rm40501]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM40601 table -- NOT NEEDED
select *
from [powmt]..[rm40601]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM50100 table 
select *
from [powmt]..[rm50100]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM50101 table -- NOT NEEDED
select *
from [powmt]..[rm50101]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM50102 table 
select *
from [powmt]..[rm50102]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM50103 table 
select *
from [powmt]..[rm50103]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

-- Check the RM50104 table 
select *
from [powmt]..[rm50104]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

Open in new window

0
Comment
Question by:rwheeler23
  • 5
  • 5
10 Comments
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36894594
You have the start of it, although instead of IN I would go for EXISTS, noting that foreign key references come into play; therefore, ensuring the order of the deletions is correct will be key, plus performance may be impacted by checking of references as it deletes.

So, instead of:
select *
from [powmt]..[rm00101]
where custnmbr in (
select custnmbr from [powmatqp]..[Customers_to_Delete])
order by custnmbr

Start with:
select *
from [powmt]..[rm00101] t
where  exists (
   select 1
   from [powmatqp]..[Customers_to_Delete] d
   where d.custnmbr = t.custnmbr
)
;

If the bit about the RM1 and RM2 is not taken care of in the selection that created the Customers_To_Delete table, then adjust WHERE accordingly OR wrap the entire transaction with an IF and check using EXISTS for any instance of the RM1|RM2 customer records.

Once everything looks as it should, just change the SELECT * to DELETE. :)

Hope that helps!
0
 

Author Comment

by:rwheeler23
ID: 36894676
What I am trying to do is clean up old data and for customers who have not ordered anything for years to delete their records. The reference to RM1 or RM2 tables means there is recent history and I do not what them to be deleted from any of the tables. What I should probably do is create an indicator variable and if any of those tables have records for the customer set it to TRUE and then for all the other tables only issue the Delete statement if the value of this variable is FALSE.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36895058
Either way, the approach is the same. You are either looking for values that EXISTS in a list based on bad records criteria or a NOT EXISTS lookup on a set of rows that qualify as good rows to keep. It does not change the DELETE syntax and caveats unless I am missing some point you are making here.
0
 

Author Comment

by:rwheeler23
ID: 36895107
What I means is this:

Let's say I find records in the RM10101 table or the RM20101 table for any of the customer in the Customers_To_Delete table. This means these customers have current activity.  Hence, I do not want any records deleted in any table.

If there are no records in either of those two tables, then I want records deleted in all tables.
This is what I meant by running queries on the RM1* and RM2* series tables first to set an indicator variable to TRUE or FALSE. If TRUE, do no deletes, if FASLE, do all the deletes.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36895139
Okay, why did the customers make it to the list to delete in the first place if they are still valid. I would do that check up front and make your life easy with one check. If not, as I said, you can amend the WHERE clause to also check that the customer number NOT EXISTS({query to test RM1 and RM2}).
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:rwheeler23
ID: 36895340
The user was given a list of potential customers who may or may not have activity. My role was to make it happen. I want to exclude anyone on the list that does have current activity and to tell them they could not be removed because of current activity. Management just had a list and gave it to the receptionist. I just want to make sure they do not shoot themselves in the foot. Yes, I could just delete them all but then I have to deal with the fact that some perhaps should not have been deleted. The list was created by non-technical people.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36895371
I am not suggesting you ignore the "right thing" to do here. What I am suggesting is, purify the rows in Customers_To_Delete before starting the process. Even if this is a manually generated list, you obviously have done the hard part of getting it into SQL. Now that you have, there is nothing stopping you from trimming that list, correct? Or create a different table that represents the real list of customers, e.g., Verified_Customers_To_Delete that is a subselection of Customers_To_Delete that do meet the criteria of NOT EXISTS({row in RM1|RM2}). Then use the new trimmed list of valid rows to delete with the syntax you already have. Or simply add the NOT EXISTS() to every delete.
0
 

Author Comment

by:rwheeler23
ID: 36895389
That gives me a great idea. I can pre-parse this table and then hand management a list of customers that do have current activity and ask them if they really want these customers to be deleted. I will have them sign off on it so if one goes that should not have gone I can simply say "I am only doing what you told me to do".

Great idea....Thanks!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36895407
You got it. As you said, just because they gave the list does not mean it is 100% accurate. Often times, this is going based on invalid criteria like last order date. If this set of customers uses blanket orders, then the order date may be from 1+ years ago. Therefore, the customer may be getting product from you every month for the last three years, but show as not having placed a new order in three years. :) It would be very embarrassing to delete that customer.
0
 

Author Closing Comment

by:rwheeler23
ID: 36895417
Very precise and provided valuable additional insight. Top notch!
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now