Union or Otherwise Combine Multiple Delete Queries

[RiskWatch]

I have 3 separate delete queries that I would like to either : Union together so they can be run in one click or re-write so that I can perform all the actions in a single query. I have so far been unable to do so. The queries are as follows:

Delete Duplicate Leads

DELETE WorkBook.*, Exists (select 1 
       from Leads 
WHERE WorkBook.Email=Leads.Email) AS Expr1
FROM WorkBook
WHERE (Exists (select 1 
       from Leads
WHERE WorkBook.Email=Leads.Email));

Delete Customers from WorkBook

DELETE WorkBook.*, Exists (select 1 
       from Customers
WHERE WorkBook.Company Like "*" & [Customers].[Company] & "*";) AS Expr1
FROM WorkBook
WHERE (((Exists (select 1 
       from Customers
WHERE WorkBook.Company Like "*" & [Customers].[Company] & "*";))<>False));

Delete Competitors from WorkBook

DELETE WorkBook.*, Exists (select 1 
       from Competitors
WHERE WorkBook.Company Like "*" & [Competitors].[Company] & "*";) AS Expr1
FROM WorkBook
WHERE (((Exists (select 1 
       from Competitors
WHERE WorkBook.Company Like "*" & [Competitors].[Company] & "*";))<>False));

Any assistance on how to combine, create a union of or rewrite these queries into a single item would be greatly appreciated.

[zmbd]

RiskWatch,

+ Personally, I would flag this information as archived, at least a yes/no field as "active"; however, I've been using a numeric field as of late (Based on Mr. Browne's Why I stopped using Yes/No fields article). You can then simply exclude the data in query based on this field for your reports etc.

+ It really looks like your database isn't normalized.
Reading the queries, it appears that the same data may be in more than one table. While there are exceptions to the normalization rule these exceptions can lead to some very difficult to manage databases and complex queries
You might take a look at: Database Normalization and Table Structures

+ Are your posted queries actually from your database? There are a few extra ";" in them and I've never seen the Exists predicate in the field section of a DELETE clause before. So removing the "extra" stuff and combining the WHERE clauses, I come up with the following:
(please backup your data before trying this :) )

>>This is "air code" I've not tried it nor checked for syntax errors.<<

DELETE WorkBook.*
FROM WorkBook
WHERE (Exists (
           SELECT 1 
           FROM Leads
           WHERE WorkBook.Email=Leads.Email))
        OR (((Exists (
           SELECT 1 
           FROM Customers
           WHERE WorkBook.Company 
              Like "*" & [Customers].[Company] & "*"))<>False))
       OR (((Exists (
           SELECT 1 
           FROM Competitors
           WHERE WorkBook.Company 
             Like "*" & [Competitors].[Company] & "*"))<>False));

Hard to tell if this will work as I do not have your table design information and there may be an easier method.


+ Another option is to create either VBA or Macro script to execute the three stored queries.

[RiskWatch]

The Macro script worked perfectly for what I was trying to accomplish and was an easy fix.

[zmbd]

good deal!

Would be interested to know if the SQL I posted works in you database. You can change it to a simple select-query by changing the DELETE to SELECT it should query the potentially affected record(s).