473,691 Members | 2,374 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Union or Otherwise Combine Multiple Delete Queries

14 New Member
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
3 957
zmbd
5,501 Recognized Expert Moderator Expert
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
RiskWatch
14 New Member
The Macro script worked perfectly for what I was trying to accomplish and was an easy fix.
Sep 1 '16 #3
zmbd
5,501 Recognized Expert Moderator Expert
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

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

Similar topics

4
3680
by: musicloverlch | last post by:
I have a table with multiple records in it. I am being forced to combine multiple records into one record in order to be uploaded to a website, and I only get one record per client. How can I systematically combine multiple records into 1? The table looks like this Client ID Service Status 1 Doc Pending 1 Test Pending
4
5742
by: Jason Gyetko | last post by:
Is there any way to combine these two queries into one? I have tables Item_Master & Kit_Master which are the source tables. Query 2 is using both Item_Master (table) & qryKit1 (query) & Kit_Master (table) as its record source because the Kit_Master table has 2 fields in it that get joined back to the same field in Item_Master. Item_Master key field is SKU Kit_Master key fields are KitNo & ComponentNo Item_Master - SKU contains a...
0
942
by: pieandpeas | last post by:
I have multiple sql queries which show mostly total values (counts(*)'s etc..) based on a table of information some of the queries bring back less matching items from the table than others if i wanted to show the following, ¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦sqlquery1 sqlquery2 sqlquery3 sqlquery4 Name1 2 NULL 321 13 name2 32 1 null 41 Name3 3 1 null 4
1
2107
by: kababoom | last post by:
Is it possible to combine multiple sql queries into 1? or does anyone has any sites or reference on how to do this? Thks for any info :)
4
2577
werks
by: werks | last post by:
Hello experts could someone tell me how can i combine multiple records into one value. Sample in MS Access: (Lastname & "" & "," & " " & Firstname) AS Name How can i do this in MySQL?..tnx in advance -- Kenneth "Better Than Yesterday"
2
1604
by: bips2005 | last post by:
i have got two queries, $sql1 = "select cust_id from customer where comp_name = '$compname'"; $result = $DB->query($sql1); $result->fetchInto($row); $sql = "select expirydate from customer l,domain s where l.cust_id = '$row' and s.domname = '$domname'"; the first query retrieves cust_id from the table customer and i use it in the second query to obtain expirydate.
1
1472
atksamy
by: atksamy | last post by:
Hi, I am running a few delete queries which r getting executed without any error. But the data in tables are not getting deleted. It is as if the queries were not run. I ran them through debugging but they are executing. Any ideas what is wrong? thanks
0
1509
by: Taiseer Hussein | last post by:
Hi, I have a project to combine multiple word doc in single one. I was able to combine them successfully using copy & paste, however I'm having two issues 1. I wanted to keep the original format, therefore I'm using oWordApplication.Selection.PasteAndFormat(Microsoft.Office.Interop.Word. WdRecoveryType .wdFormatOriginalFormatting); which keeps the original formatting. 2. The second issue is the numbering in which it continues from the...
5
1294
by: DanicaDear | last post by:
I work for an electric utility and when a storm blows through, we clear out our Storm database so we can make new work assignments. To do this, we open a database and run 5 delete queries followed by 3 append queries. I want one button to run all 5 delete queries followed by the 3 append queries. I noticed when I originally wrote the code that I got a delete query warning for ALL FIVE delete queries. I think the warning is necessary but...
27
16182
by: postman | last post by:
I need to combine multiple records into one row with multiple columns for those records. Very similar to a crosstab query, but one that shows all the records, not a summary of them. For example, if the table is like this: Name Month Score John Jan 92 John Jan 90 John Feb 97 John Feb 91
0
8599
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9079
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8791
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8794
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7623
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4550
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2965
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2227
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1952
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.