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
7  2995  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
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 )))
FishVal 2,653
Recognized Expert Specialist
Something like. -
DELETE *
-
FROM x
-
WHERE (((x.ID) In (SELECT [ID] FROM [x] As Tmp GROUP BY [ID] HAVING Count(*)>1 )))
-
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
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.
-
DELETE *
-
FROM x
-
WHERE (((x.ID) In (SELECT [ID] FROM [x] As Tmp GROUP BY [ID] HAVING Count(*)>1 )))
-
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
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
FishVal 2,653
Recognized Expert Specialist
Hi, Jedd.
You may design a query to return records to be deleted.
Something like: -
SELECT Min([Total value]) FROM x GROUP BY [ID] HAVING Count(*) > 1;
-
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.
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:
-
SELECT Min([Total value]) FROM x GROUP BY [ID] HAVING Count(*) > 1;
-
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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:
...
|
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)
|
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...
|
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)
|
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...
|
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...
|
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...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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...
|
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,...
|
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,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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 ...
| |
