473,703 Members | 2,331 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Best way for finding duplicate entries in table?

Hi all,

since a few years I use the following statement to find duplicate
entries in a table:

SELECT t1.id, t2.id,...
FROM table AS t1 INNER JOIN table AS t2
ON t1.field = t2.field
WHERE t1.id < t2.id

Of course Indexes are set on id (Primary Key, Autoincrement) and field
(normal Index) and are, according to EXPLAIN, used (index "field").

This statement works quite well but takes ages (> 10 minutes) of time
when using it on a table with tenthousands of rows.

Now my question:
Are there better and especially faster ways to find duplicate entries
within a table without having to create a seperate table which will be
filled with a SELECT DISTINCT... statement from the "old" table?

Thank's in advance for your help and time,

Andy Maurer

Jul 23 '05 #1
3 6932
an************* ***@web.de wrote:
Now my question:
Are there better and especially faster ways to find duplicate entries
within a table without having to create a seperate table which will be
filled with a SELECT DISTINCT... statement from the "old" table?


select id,count(field) as amount
from table group by field having amount > 1;
Jul 23 '05 #2


Aggro schrieb:
an************* ***@web.de wrote:
Now my question:
Are there better and especially faster ways to find duplicate entries
within a table without having to create a seperate table which will be
filled with a SELECT DISTINCT... statement from the "old" table?


select id,count(field) as amount
from table group by field having amount > 1;


Hi Aggro,

thank you very much for your help, it shows me duplicate entries but,
as well due to a not better specified question of mine, some problems
are left open.
First of all your statement, when issued in phpmyadmin, tells me that
it found in total all rows of the table. Do you have any idea, why?
Another problem I have is: It shows me the duplicate entries in "field"
but only one of the many keys related to this entry. Therefore, if I
wanted to delete all but one of these entries, I have to issue a lot of
SELECT and DELETE statements in order to clean my table.

Of course I always could create another (temporary) table and fill it
with DISTINCT entries of table1 but this is sometimes not an option for
me.

Do you have another idea how to fix this?

Thank's again,

Andy

Jul 23 '05 #3
an************* ***@web.de wrote:
select id,count(field) as amount
from table group by field having amount > 1;
Another problem I have is: It shows me the duplicate entries in "field"
but only one of the many keys related to this entry. Therefore, if I
wanted to delete all but one of these entries, I have to issue a lot of
SELECT and DELETE statements in order to clean my table.


This should give the list of fields that occur more than once in the
database:

SELECT t2.`field`
FROM `table` AS t2
GROUP BY t2.`field`
HAVING COUNT(t2.`field `) > 1;

This should give the list of id's in whose records those values for
field occur:

SELECT t.id
FROM `table` AS t
WHERE t.`field` IN (
SELECT t2.`field`
FROM `table` AS t2
GROUP BY t2.`field`
HAVING COUNT(t2.`field `) > 1);

This should give the list of the lowest id for each given value in field:

SELECT MIN(t3.id)
FROM `table` AS t3
GROUP BY t3.`field`;

Putting it all together, this should delete records who contain values
for field that appear on more than one row, but do not delete such
records with the lowest id for a given value of `field`:

DELETE FROM `table`
WHERE id IN (
SELECT t.id
FROM `table` AS t
WHERE t.`field` IN (
SELECT t2.`field`
FROM `table` AS t2
GROUP BY t2.`field`
HAVING COUNT(t2.`field `) > 1))
AND id NOT IN (
SELECT MIN(t3.id)
FROM `table` AS t3
GROUP BY t3.`field`);

I'm assuming that you want to keep each record with the lowest id value.
In order to do this in a single operation, you must have some kind of
uniform policy like this, that applies to all cases. You could use
MAX(t3.id) instead to delete all but the most recent records, but other
than those two options, the alternatives get more complex to implement.

Regards,
Bill K.
Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
3104
by: Gary Lundquest | last post by:
I have an application with MS Access 2000 as the front end and MySQL as the back end. All was well until I upgraded the MySQL (Linux) server. The Problem: I insert data into a cumulative table. Before when I did this, and there were duplicate entries, the duplicate entries were rejected and I got a return code with the number of affected rows (number added). Now, I get a MyODBC error and the application stops when trying to add...
18
27714
by: Elroyskimms | last post by:
I have a table using an identity column as its Primary Key and two columns (table reduced for simplicity) EmployeeNumber and ArrivalTime. CREATE TABLE ( IDENTITY (1, 1) NOT NULL , (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , NOT NULL , CONSTRAINT PRIMARY KEY CLUSTERED (
0
1178
by: Trey | last post by:
I am trying to make a database for tracking calls, the caller gets one free call (per 12 months), then is charged a flat fee for every call after the first call in a 12 month time span. The data is imported into the database weekly (about 120 calls a week) from a text file. I am able to query the 12-month part. But I am having trouble making a statement/macro/query to check for duplicate entries with the same address in the database, with a...
0
1817
by: Cyberwolf | last post by:
OK, how to explain this. I have a table that will feed into another table using an append query. What I want to do is look at the table I am appending the record to to check for a duplicate record. I would use a key field, but, I will sometimes want to allow the duplicate field. i.e. sometimes it will have a zero (0) value. So how would I check the other table once I click on the button to add the record. I have a Current TB table...
2
28902
by: Carroll | last post by:
I'm looking for a way in SQL to find duplicate records in a single table, that are the same based on 3 columns, regardless of what is in the other columns in the duplicate records. I would like to keep both records (or it could be more than 2 as well) where duplicate records are found. Also, I am interested in selecting all columns from the duplicate records. Thanks, Carroll Rinehart
2
45867
by: mivey4 | last post by:
Okay I have 2 tables: Table A - holds a list of new hardware serial numbers and their corresponding model (no constraints or indexes) Table B - holds a distinct list of current serial numbers and the corresponding model numbers (primary key on serial_numbers) Since Table A has no constraints duplicates may exist. Additionally, table A is actually an Excel spreadsheet that is maintained by an employee that records new hardware as...
8
5876
by: desirocks | last post by:
Hi all, I am somewhat new to sql server. So help me in whatever way you can. I have two tables one of which doesnt have a primary key(table A) and other has a composite key formed of two columns(table B). I need to insert entries in table B from table A(table A has a unique constratint UID which is passed to table B). Table B also has a unique ID which is different from UID of table A. Sometimes table A tries to insert a duplicate...
4
2904
by: ramdil | last post by:
Hi All I have table and it have around 90000 records.Its primary key is autonumber field and it has also have date column and name, then some other columns Now i have problem with the table,as my table contains duplicate entries for a particular date.How can i delete the duplicate entries from the table for that particular column,Now i am doing manually with name column as it will be unique for that date.Can any one help me giving the query...
7
2930
by: php_mysql_beginer911 | last post by:
Hi .. i am trying to update a table where if field contents any duplictaed entries than one of the field should be updated with random number which is unique so i can make all entries unique i searched a lot but couldn't find any solution which i could understand easily . is it very difficult in sql to update duplicate entries with new unique random values? table example ----------------------
0
8669
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
9251
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
9122
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9017
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
8963
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...
1
6588
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
5922
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 then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4433
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...
0
4687
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

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.