469,138 Members | 1,257 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,138 developers. It's quick & easy.

Union or Otherwise Combine Multiple Delete Queries

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

Expand|Select|Wrap|Line Numbers
  1. DELETE WorkBook.*, Exists (select 1 
  2.        from Leads 
  3. WHERE WorkBook.Email=Leads.Email) AS Expr1
  4. FROM WorkBook
  5. WHERE (Exists (select 1 
  6.        from Leads
  7. WHERE WorkBook.Email=Leads.Email));
  8.  
Delete Customers from WorkBook

Expand|Select|Wrap|Line Numbers
  1. DELETE WorkBook.*, Exists (select 1 
  2.        from Customers
  3. WHERE WorkBook.Company Like "*" & [Customers].[Company] & "*";) AS Expr1
  4. FROM WorkBook
  5. WHERE (((Exists (select 1 
  6.        from Customers
  7. WHERE WorkBook.Company Like "*" & [Customers].[Company] & "*";))<>False));
  8.  
Delete Competitors from WorkBook
Expand|Select|Wrap|Line Numbers
  1. DELETE WorkBook.*, Exists (select 1 
  2.        from Competitors
  3. WHERE WorkBook.Company Like "*" & [Competitors].[Company] & "*";) AS Expr1
  4. FROM WorkBook
  5. WHERE (((Exists (select 1 
  6.        from Competitors
  7. WHERE WorkBook.Company Like "*" & [Competitors].[Company] & "*";))<>False));
  8.  
Any assistance on how to combine, create a union of or rewrite these queries into a single item would be greatly appreciated.
Aug 31 '16 #1

✓ answered by 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.<<
Expand|Select|Wrap|Line Numbers
  1. DELETE WorkBook.*
  2. FROM WorkBook
  3. WHERE (Exists (
  4.            SELECT 1 
  5.            FROM Leads
  6.            WHERE WorkBook.Email=Leads.Email))
  7.         OR (((Exists (
  8.            SELECT 1 
  9.            FROM Customers
  10.            WHERE WorkBook.Company 
  11.               Like "*" & [Customers].[Company] & "*"))<>False))
  12.        OR (((Exists (
  13.            SELECT 1 
  14.            FROM Competitors
  15.            WHERE WorkBook.Company 
  16.              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.

3 760
zmbd
5,400 Expert Mod 4TB
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.<<
Expand|Select|Wrap|Line Numbers
  1. DELETE WorkBook.*
  2. FROM WorkBook
  3. WHERE (Exists (
  4.            SELECT 1 
  5.            FROM Leads
  6.            WHERE WorkBook.Email=Leads.Email))
  7.         OR (((Exists (
  8.            SELECT 1 
  9.            FROM Customers
  10.            WHERE WorkBook.Company 
  11.               Like "*" & [Customers].[Company] & "*"))<>False))
  12.        OR (((Exists (
  13.            SELECT 1 
  14.            FROM Competitors
  15.            WHERE WorkBook.Company 
  16.              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.
Aug 31 '16 #2
The Macro script worked perfectly for what I was trying to accomplish and was an easy fix.
Sep 1 '16 #3
zmbd
5,400 Expert Mod 4TB
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).
Sep 1 '16 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by musicloverlch | last post: by
4 posts views Thread by Jason Gyetko | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.