473,887 Members | 2,327 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Delete Similar Records

8 New Member
I am having trouble creating a query that will delete duplicate records. It seems to me that this isn't that hard of a query, but I just can't seem to get it working. What I have is a table that has an ID, which should be unique. Unfortunately there have been entries for the same ID where the Total Value row has been increased. What I want to do is delete the records that have the same ID number but the lower total.

Here is one of the sql queries I wrote to try and do this that didn't work...

delete *
FROM x, x AS x_1
where x.id = x_1.id
and x.Total Value > x_1.Total Value
Or x.Total Vale<x_1.Total Value

Any ideas are appreciated
Apr 8 '08 #1
7 3029
FishVal
2,653 Recognized Expert Specialist
Hi, there.

Recordset provided by table self-joining is not updatable, thus no records could be deleted. You should use subquery instead. Run Access wizard for "Find duplicates" query and see how it may be implemented.

Regards,
Fish
Apr 8 '08 #2
jedgretzky
8 New Member
After running the subquery to come up with the duplicated data fields, It comes up with all of the rows with duplicate IDs. However I am not sure how to add in the argument to delete certain rows only where the total amount is the largest. Here is what the SQL looked like from this query to find the duplicates.

select x.ID, x.[TOTAL VALUE], x.SORTNAME, x.[DECEASED FLAG], x.NAME, x.[FACE VALUE], x.[TOTAL VALUE]
FROM x
WHERE (((x.ID) In (SELECT [ID] FROM [x] As Tmp GROUP BY [ID] HAVING Count(*)>1 )))
Apr 9 '08 #3
FishVal
2,653 Recognized Expert Specialist
Something like.

Expand|Select|Wrap|Line Numbers
  1. DELETE *
  2. FROM x
  3. WHERE (((x.ID) In (SELECT [ID] FROM [x] As Tmp GROUP BY [ID] HAVING Count(*)>1 )))
  4.  
This will however delete both duplicates. Guess that is not what you really want.
So the question is how a record to be deleted would be recognized?

Regards,
Fish
Apr 9 '08 #4
jedgretzky
8 New Member
The record would have to be identified by the Total_Value field. In the duplicates there will be 2 different values, one Total_Value will be smaller than the other Total_Value field. I want to delete the record that has the smaller Total_Value field.

Here is one idea that I tried but could not get working: This shows basically how I wanted to delete the record where the value is smaller.

delete *
from x T1, x T2
where T1.id = T2.id
and T1.Total Value > T2.Total Value

It almost seems to me that I might need to create an identical table of the data and basically compare the two tables to delete the correct fields.

-Jedd


Something like.

Expand|Select|Wrap|Line Numbers
  1. DELETE *
  2. FROM x
  3. WHERE (((x.ID) In (SELECT [ID] FROM [x] As Tmp GROUP BY [ID] HAVING Count(*)>1 )))
  4.  
This will however delete both duplicates. Guess that is not what you really want.
So the question is how a record to be deleted would be recognized?

Regards,
Fish
Apr 9 '08 #5
jedgretzky
8 New Member
I have a select query that selects the records that I want. I think I may just have to enter a delete statement inplace of the select. Unfortunately I will be unable to test this today, it is quiting time.

This is what the select statement looks like:

SELECT x.ID, x.[TOTAL VALUE]
FROM x INNER JOIN x AS x_1 ON x.ID = x_1.ID
WHERE x.[Total Value]< x_1.[Total Value];


Thank you for your assistance with my questions, it definately helped.

-Jedd
Apr 9 '08 #6
FishVal
2,653 Recognized Expert Specialist
Hi, Jedd.

You may design a query to return records to be deleted.
Something like:
Expand|Select|Wrap|Line Numbers
  1. SELECT Min([Total value]) FROM x GROUP BY [ID] HAVING Count(*) > 1;
  2.  
Then use it in delete query as criteria using IN (subquery) syntax in WHERE clause to check whether a record belongs to to-be-deleted recordset.

Regards,
Fish.
Apr 9 '08 #7
jedgretzky
8 New Member
I just tested the query and it worked perfectly, thanks for your help I really appreciate it.

-Jedd

Hi, Jedd.

You may design a query to return records to be deleted.
Something like:
Expand|Select|Wrap|Line Numbers
  1. SELECT Min([Total value]) FROM x GROUP BY [ID] HAVING Count(*) > 1;
  2.  
Then use it in delete query as criteria using IN (subquery) syntax in WHERE clause to check whether a record belongs to to-be-deleted recordset.

Regards,
Fish.
Apr 14 '08 #8

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

Similar topics

4
1994
by: Belinda | last post by:
Hello All I need to read a SQL Server table into a Web Page and within the Web Page to permit my users to make changes to the records, delete or add new records and then save the entire contents back to the SQL Server table back. The functionality I am looking is almost the same as In the SQL Enterprise Manager whereby I can choose a table open the table and then return all rows and I can maintain the same and save it back to
0
1068
by: Jack Coxen | last post by:
------_=_NextPart_001_01C3671C.01DAD650 Content-Type: text/plain; charset="iso-8859-1" I'm in the process of writing a Perl script to delete records out of a database when the records are more than 90 days old. I'm running MySQL version 4.0.12-standard. The db consists of around 620 MyISAM tables with the following structure: CREATE TABLE `ifInErrors_2` (
8
4076
by: Steve | last post by:
I have several pairs of synchronized subforms in an application. I have a Delete button for each pair that uses the following code or similar to delete a record in the second subform: DoCmd.SetWarnings False DoCmd.RunCommand acCmdDeleteRecord DoCmd.SetWarnings True End If ExitHere: Me!SubName.SetFocus
5
5045
by: hpi | last post by:
Hello, I have a table : Batch It contains fields batchnummer : Number (Long Integer) datum : Date/Time status : Number (Long Integer) nr_records : Number (Long Integer)
2
1556
by: Dalan | last post by:
Having searched the archives regarding a Delete Query, I found nothing specific to my need, although there seems to be a plethora of ideas and suggestions on queries in general. I have used Delete Queries based on date selection parameters and those have performed fine, but perhaps a parameter is not what I should use for the following. Simply, I need to provide a method by which to query two fields and delete all records associated with...
5
11107
by: PaulMac | last post by:
Hi, Hopefully this is a simple one. IF x =3 then i want to delete the first 3 records in tabley (Similar to a TOP select statement: Select Top 3 * from tabley)
6
3110
by: Paul T. Rong | last post by:
Dear all, Here is my problem: There is a table "products" in my access database, since some of the products are out of date and stopped manufacture, I would like to delete those PRODUCTS from the table, but I was not allowed to do that, because "there are records related with those PRODUCTS in other tables (e.g. in table "ORDER_DETAIL").
2
8857
by: Cornelius Buschka | last post by:
Hi, we saw the following problem: We deleted all rows from a table B referencing table A (~500000 records). No problem, but the following try to delete all records from table A (~180000) lead to a "never ending" statement. We found out, that vacuuming table B after delete did the trick. It seems to us the database has to do scan thru deleted records on B while
12
3384
by: yufufi | last post by:
Hello, How does delete know how much memory to deallocate from the given pointer? AFAIK this informations is put there by new. new puts the size of the allocated memory before the just before the beginning of the array. But I couldn't find this information by looking at the memory. (Via VS2005 - C++) My second questions is, if there is a mechanism to know how much memory is allocated for the array, why don't we use it for things like...
0
9957
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
9799
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11173
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...
0
10432
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
9593
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...
1
7988
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5809
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4238
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3245
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.