asked on

SQL Script to Delete records from multiple tables

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

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwheeler23

ASKER

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.

Kevin Cross

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.

rwheeler23

ASKER

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.

Kevin Cross

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}).

rwheeler23

ASKER

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.

Kevin Cross

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.

rwheeler23

ASKER

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!

Kevin Cross

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.

rwheeler23

ASKER

Very precise and provided valuable additional insight. Top notch!