Re: Deleting Duplicate Records

[Rahul S. Johari]

That worked Perfect!!!
Thanks a ton!

On 11/8/05 11:27 AM, "Shen139" <[EMAIL PROTECTED]> wrote:

> ALTER IGNORE TABLE mytbl ADD UNIQUE KEY ( Name, email, sex, country ) ;


Rahul S. Johari
Coordinator, Internet & Administration
Informed Marketing Services Inc.
251 River Street
Troy, NY 12180

Tel: (518) 266-0909 x154
Fax: (518) 266-0909
Email: [EMAIL PROTECTED]
http://www.informed-sources.com

Re: Deleting Duplicate Records

[Rahul S. Johari]

Hi,

It¹s didn¹t work. I think I know why.
I have 5 fields in my table. ID, name, email, sex, country.
Although in the duplicate rows.. Name, email, sex & country values are
duplicate, the value in the ID column is different for each. It happened so
when people added their entry multiple times. The ID was new each time,
however everything else was the same.

Your code would probably eliminate all Duplicate rows from a table. But
since the ID is different in each of those records, none of those records
actually classify as a Duplicate ROW.

Any other way?

Thanks.


On 11/8/05 11:10 AM, "Shen139" <[EMAIL PROTECTED]> wrote:

> ALTER IGNORE TABLE mytbl ADD UNIQUE KEY ( field1, field2,... ) ;
> 
> On 11/8/05, Rahul S. Johari <[EMAIL PROTECTED]
>  > wrote:
>> 
>> Hi,
>> 
>> I used the following SQL Query to determine Duplicate Records with same Name
>> & Email in the row:
>> 
>> SELECT email,
>> COUNT(email) AS NumOccur1, name, COUNT(name) AS NumOccur2
>> FROM mytbl
>> GROUP BY name, email
>> HAVING ( COUNT(email) > 1 ) AND ( COUNT(name) > 1 )
>> 
>> 
>> I want to delete all the records which are duplicate, leaving the original
>> record as it is. How can I delete Only the Duplicate records?
>> 
>> Thanks.
>> 
>> 
>> 
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>> 
> 
> 


Rahul S. Johari
Coordinator, Internet & Administration
Informed Marketing Services Inc.
251 River Street
Troy, NY 12180

Tel: (518) 266-0909 x154
Fax: (518) 266-0909
Email: [EMAIL PROTECTED]
http://www.informed-sources.com

Deleting Duplicate Records

[Rahul S. Johari]

Hi,

I used the following SQL Query to determine Duplicate Records with same Name
& Email in the row:

SELECT email, 
COUNT(email) AS NumOccur1, name, COUNT(name) AS NumOccur2
FROM mytbl
GROUP BY name, email
HAVING ( COUNT(email) > 1 ) AND ( COUNT(name) > 1 )


I want to delete all the records which are duplicate, leaving the original
record as it is. How can I delete Only the Duplicate records?

Thanks.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Deleting Duplicate Records

[mos]

At 02:32 PM 7/30/2003, you wrote:
Group,

I have been working on a project for a while now trying to figure out how 
to remove duplicate records from a single table using a query.  To 
complicate matters, what constitutes a duplicate record is a match on 
several fields, but not all fields.  I have been successful in matching 
all duplicates based on the fields I'm interested in, but this only 
returns the duplicate records.  I want a query that will return all 
records from the table, but only once if certain fields are the same.

The table structure is basically:
User Code
Date
Last Name
First Name
Address
City
State
ZIP
and then  some other fields
 I don't want to use the values in the other fields to determine if the 
record duplicates another.  I'm not concerned about which one of the 
duplicate records I keep because I will make a backup copy of this table 
before removing the duplicates, so I will still have all the data from 
the records deleted.  Later I will redesign the tables for this database 
and link back in the data from the other fields.

So I need a method that will output one and only one record from the table 
in which the data in the above mentioned fields are the same.
Again, even if the data in the remaining fields are different I just want 
one of the records, and I don't care which one.

Thanks for your help
Kim Mackey
Kim,

Here are a few solutions on the web:
http://www.databasejournal.com/features/mysql/article.php/10897_2201621_3
http://15seconds.com/issue/011009.htm
http://www.faqchest.com/prgm/mysql-l/mysql-03/mysql-0302/mysql-030222/mysql03021508_15076.html
http://www.experts-exchange.com/Databases/Mysql/Q_20610046.html
Mike



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Deleting Duplicate Records

[Paul B van den Berg]

To select records only once try something like

select * from your_table
group by field1, field2, ..

Regards, Paul

Kim Mackey wrote:

 > Group,
 >
 > I have been working on a project for a while now trying to figure out 
 > how to remove duplicate records from a single table using a query.  To 
 > complicate matters, what constitutes a duplicate record is a match on 
 > several fields, but not all fields.  I have been successful in 
 > matching all duplicates based on the fields I'm interested in, but 
 > this only returns the duplicate records.  I want a query that will 
 > return all records from the table, but only once if certain fields are 
 > the same.
 >
 > The table structure is basically:
 > User Code
 > Date
 > Last Name
 > First Name
 > Address
 > City
 > State
 > ZIP
 > and then  some other fields
 >
 >  I don't want to use the values in the other fields to determine if 
 > the record duplicates another.  I'm not concerned about which one of 
 > the duplicate records I keep because I will make a backup copy of this 
 > table before removing the duplicates, so I will still have all the 
 > data from the records deleted.  Later I will redesign the tables for 
 > this database and link back in the data from the other fields.
 >
 > So I need a method that will output one and only one record from the 
 > table in which the data in the above mentioned fields are the same.  
 > Again, even if the data in the remaining fields are different I just 
 > want one of the records, and I don't care which one.
 >
 > Thanks for your help
 > Kim Mackey
 >
 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Deleting Duplicate Records

[Primaria Falticeni]

The cleanest way is to add a column (ukey) to the table which will uniquely
identify each record.
This will help you in the following phases too. Let's say the name of your
table is 'focus_table'.

1.adding the unique key 'ukey':
ALTER TABLE 'focus_table'
ADD COLUMN
ukey BIGINT(12) AUTO_INCREMENT PRIMARY KEY;

*Now you know what records are remaining after deleting the duplicates and
the table will have the same structure.

2.backup your table
3.select the duplicates:
CREATE TEMPORARY TABLE dupls
SELECT * FROM 'focus_table'
GROUP BY field1,field2,... ;

*field1,field2,... is the list of the fields which give the duplicates.

4.empty the table:
DELETE FROM 'focus_table';

5.fill it with unique records:
INSERT INTO 'focus_table'
SELECT * FROM dupls;

- Original Message -
From: "Cybot" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, July 31, 2003 11:41 AM
Subject: Re: Deleting Duplicate Records


> Kim Mackey wrote:
> > Group,
> >
> > I have been working on a project for a while now trying to figure out
> > how to remove duplicate records from a single table using a query.  To
> > complicate matters, what constitutes a duplicate record is a match on
> > several fields, but not all fields.  I have been successful in matching
> > all duplicates based on the fields I'm interested in, but this only
> > returns the duplicate records.  I want a query that will return all
> > records from the table, but only once if certain fields are the same.
> >
> > The table structure is basically:
> > User Code
> > Date
> > Last Name
> > First Name
> > Address
> > City
> > State
> > ZIP
> > and then  some other fields
> >
> >  I don't want to use the values in the other fields to determine if the
> > record duplicates another.  I'm not concerned about which one of the
> > duplicate records I keep because I will make a backup copy of this table
> > before removing the duplicates, so I will still have all the data from
> > the records deleted.  Later I will redesign the tables for this database
> > and link back in the data from the other fields.
> >
> > So I need a method that will output one and only one record from the
> > table in which the data in the above mentioned fields are the same.
> > Again, even if the data in the remaining fields are different I just
> > want one of the records, and I don't care which one.
>
> CREATE TABLE `new_table`
> SELECT *
> FROM `old_table`
> GROUP BY [your_fields_you_want_unique];
>
> --
> Sebastian Mendel
>
> www.sebastianmendel.de
> www.tekkno4u.de
> www.nofetish.com
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Deleting Duplicate Records

[Cybot]

Kim Mackey wrote:
Group,

I have been working on a project for a while now trying to figure out 
how to remove duplicate records from a single table using a query.  To 
complicate matters, what constitutes a duplicate record is a match on 
several fields, but not all fields.  I have been successful in matching 
all duplicates based on the fields I'm interested in, but this only 
returns the duplicate records.  I want a query that will return all 
records from the table, but only once if certain fields are the same.

The table structure is basically:
User Code
Date
Last Name
First Name
Address
City
State
ZIP
and then  some other fields
 I don't want to use the values in the other fields to determine if the 
record duplicates another.  I'm not concerned about which one of the 
duplicate records I keep because I will make a backup copy of this table 
before removing the duplicates, so I will still have all the data from 
the records deleted.  Later I will redesign the tables for this database 
and link back in the data from the other fields.

So I need a method that will output one and only one record from the 
table in which the data in the above mentioned fields are the same.  
Again, even if the data in the remaining fields are different I just 
want one of the records, and I don't care which one.
CREATE TABLE `new_table`
SELECT *
FROM `old_table`
GROUP BY [your_fields_you_want_unique];
--
Sebastian Mendel
www.sebastianmendel.de
www.tekkno4u.de
www.nofetish.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Deleting Duplicate Records

[Kim Mackey]

Unfortunately this is not the case.

Any other ideas?

Kim

If all these fields are defined as not NULL, simply
use alter table with the ignore option to add a unique key on all 
pertainant fields.

Kim Mackey wrote:

Group,

I have been working on a project for a while now trying to figure out 
how to remove duplicate records from a single table using a query.  
To complicate matters, what constitutes a duplicate record is a match 
on several fields, but not all fields.  I have been successful in 
matching all duplicates based on the fields I'm interested in, but 
this only returns the duplicate records.  I want a query that will 
return all records from the table, but only once if certain fields 
are the same.

The table structure is basically:
User Code
Date
Last Name
First Name
Address
City
State
ZIP
and then  some other fields
 I don't want to use the values in the other fields to determine if 
the record duplicates another.  I'm not concerned about which one of 
the duplicate records I keep because I will make a backup copy of 
this table before removing the duplicates, so I will still have all 
the data from the records deleted.  Later I will redesign the tables 
for this database and link back in the data from the other fields.

So I need a method that will output one and only one record from the 
table in which the data in the above mentioned fields are the same.  
Again, even if the data in the remaining fields are different I just 
want one of the records, and I don't care which one.

Thanks for your help
Kim Mackey



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Deleting Duplicate Records

[gerald_clark]

If all these fields are defined as not NULL, simply
use alter table with the ignore option to add a unique key on all 
pertainant fields.

Kim Mackey wrote:

Group,

I have been working on a project for a while now trying to figure out 
how to remove duplicate records from a single table using a query.  To 
complicate matters, what constitutes a duplicate record is a match on 
several fields, but not all fields.  I have been successful in 
matching all duplicates based on the fields I'm interested in, but 
this only returns the duplicate records.  I want a query that will 
return all records from the table, but only once if certain fields are 
the same.

The table structure is basically:
User Code
Date
Last Name
First Name
Address
City
State
ZIP
and then  some other fields
 I don't want to use the values in the other fields to determine if 
the record duplicates another.  I'm not concerned about which one of 
the duplicate records I keep because I will make a backup copy of this 
table before removing the duplicates, so I will still have all the 
data from the records deleted.  Later I will redesign the tables for 
this database and link back in the data from the other fields.

So I need a method that will output one and only one record from the 
table in which the data in the above mentioned fields are the same.  
Again, even if the data in the remaining fields are different I just 
want one of the records, and I don't care which one.

Thanks for your help
Kim Mackey



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Deleting Duplicate Records

[Kim Mackey]

Group,

I have been working on a project for a while now trying to figure out 
how to remove duplicate records from a single table using a query.  To 
complicate matters, what constitutes a duplicate record is a match on 
several fields, but not all fields.  I have been successful in matching 
all duplicates based on the fields I'm interested in, but this only 
returns the duplicate records.  I want a query that will return all 
records from the table, but only once if certain fields are the same.

The table structure is basically:
User Code
Date
Last Name
First Name
Address
City
State
ZIP
and then  some other fields
 I don't want to use the values in the other fields to determine if the 
record duplicates another.  I'm not concerned about which one of the 
duplicate records I keep because I will make a backup copy of this 
table before removing the duplicates, so I will still have all the data 
from the records deleted.  Later I will redesign the tables for this 
database and link back in the data from the other fields.

So I need a method that will output one and only one record from the 
table in which the data in the above mentioned fields are the same.  
Again, even if the data in the remaining fields are different I just 
want one of the records, and I don't care which one.

Thanks for your help
Kim Mackey
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Deleting duplicate records

[Natividad Castro]

That works fine, but I was wondering if there was another way to do it
because I receive one text file everyday and the are always duplicate
records. We just change to MySQL and I have to load this text file everyday
to the database.

Thanks again!!
Nato

-Original Message-
From: Paul DuBois [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 04, 2002 4:32 PM
To: Natividad Castro; [EMAIL PROTECTED]
Subject: RE: Deleting duplicate records


At 16:29 -0500 3/4/02, Natividad Castro wrote:
>Thank you very much!! that was very helpful. By the way, is there any other
>way to get rid of duplicate records from a text file?

What's wrong with the command Jeremy suggested?

>
>Thanks again
>Nato
>
>-Original Message-
>From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
>Sent: Monday, March 04, 2002 3:00 PM
>To: Natividad Castro
>Cc: [EMAIL PROTECTED]
>Subject: Re: Deleting duplicate records
>
>
>On Mon, Mar 04, 2002 at 02:48:06PM -0500, Natividad Castro wrote:
>
>>  I'm new using mysql. I'm trying to load data from a text file to one
>>  of my table. This text file contains duplicate records. My question
>>  is how do you tell mysql to delete duplicate records and load just
>>  the ones that are not duplicate? I already tried the REPLACE and
>>  IGNORE key words but is I dont get the result that I want.  Any idea
>
>If the data is in a text file, elimiate the duplicates before MySQL is
>even involved:
>
>   cat myfile.txt | sort | uniq > nodups.txt
>
>Jeremy
>--
>Jeremy D. Zawodny, <[EMAIL PROTECTED]>
>Technical Yahoo - Yahoo Finance
>Desk: (408) 349-7878   Fax: (408) 349-5454   Cell: (408) 685-5936
>
>MySQL 3.23.47-max: up 25 days, processed 842,341,115 queries (386/sec. avg)



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Deleting duplicate records

[Jeremy Zawodny]

On Mon, Mar 04, 2002 at 04:29:16PM -0500, Natividad Castro wrote:
>
> Thank you very much!! that was very helpful. By the way, is there
> any other way to get rid of duplicate records from a text file?

I can think of others, but they're not nearly as easy or efficient.

Jeremy
-- 
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878   Fax: (408) 349-5454   Cell: (408) 685-5936

MySQL 3.23.47-max: up 25 days, processed 852,316,080 queries (387/sec. avg)

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Deleting duplicate records

[Paul DuBois]

At 16:29 -0500 3/4/02, Natividad Castro wrote:
>Thank you very much!! that was very helpful. By the way, is there any other
>way to get rid of duplicate records from a text file?

What's wrong with the command Jeremy suggested?

>
>Thanks again
>Nato
>
>-Original Message-
>From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
>Sent: Monday, March 04, 2002 3:00 PM
>To: Natividad Castro
>Cc: [EMAIL PROTECTED]
>Subject: Re: Deleting duplicate records
>
>
>On Mon, Mar 04, 2002 at 02:48:06PM -0500, Natividad Castro wrote:
>
>>  I'm new using mysql. I'm trying to load data from a text file to one
>>  of my table. This text file contains duplicate records. My question
>>  is how do you tell mysql to delete duplicate records and load just
>>  the ones that are not duplicate? I already tried the REPLACE and
>>  IGNORE key words but is I dont get the result that I want.  Any idea
>
>If the data is in a text file, elimiate the duplicates before MySQL is
>even involved:
>
>   cat myfile.txt | sort | uniq > nodups.txt
>
>Jeremy
>--
>Jeremy D. Zawodny, <[EMAIL PROTECTED]>
>Technical Yahoo - Yahoo Finance
>Desk: (408) 349-7878   Fax: (408) 349-5454   Cell: (408) 685-5936
>
>MySQL 3.23.47-max: up 25 days, processed 842,341,115 queries (386/sec. avg)


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Deleting duplicate records

[Natividad Castro]

Thank you very much!! that was very helpful. By the way, is there any other
way to get rid of duplicate records from a text file?

Thanks again
Nato

-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 04, 2002 3:00 PM
To: Natividad Castro
Cc: [EMAIL PROTECTED]
Subject: Re: Deleting duplicate records


On Mon, Mar 04, 2002 at 02:48:06PM -0500, Natividad Castro wrote:

> I'm new using mysql. I'm trying to load data from a text file to one
> of my table. This text file contains duplicate records. My question
> is how do you tell mysql to delete duplicate records and load just
> the ones that are not duplicate? I already tried the REPLACE and
> IGNORE key words but is I dont get the result that I want.  Any idea

If the data is in a text file, elimiate the duplicates before MySQL is
even involved:

  cat myfile.txt | sort | uniq > nodups.txt

Jeremy
--
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878   Fax: (408) 349-5454   Cell: (408) 685-5936

MySQL 3.23.47-max: up 25 days, processed 842,341,115 queries (386/sec. avg)


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Deleting duplicate records

[[EMAIL PROTECTED]]

>  I'm new using mysql. I'm trying to load data from a text file to one of my
>  table. This text file contains duplicate records. My question is how do you
>  tell mysql to delete duplicate records and load just the ones that are not

  Why not just filter the data through uniq first?
~~
Ray B. Morris
[EMAIL PROTECTED]
http://www.webmastersguide.com

Copyright 2002 Morris, All rights reserved



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Deleting duplicate records

[Jeremy Zawodny]

On Mon, Mar 04, 2002 at 02:48:06PM -0500, Natividad Castro wrote:

> I'm new using mysql. I'm trying to load data from a text file to one
> of my table. This text file contains duplicate records. My question
> is how do you tell mysql to delete duplicate records and load just
> the ones that are not duplicate? I already tried the REPLACE and
> IGNORE key words but is I dont get the result that I want.  Any idea

If the data is in a text file, elimiate the duplicates before MySQL is
even involved:

  cat myfile.txt | sort | uniq > nodups.txt

Jeremy
-- 
Jeremy D. Zawodny, <[EMAIL PROTECTED]>
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878   Fax: (408) 349-5454   Cell: (408) 685-5936

MySQL 3.23.47-max: up 25 days, processed 842,341,115 queries (386/sec. avg)

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Deleting duplicate records

[Natividad Castro]

Hi to all,
I'm new using mysql. I'm trying to load data from a text file to one of my
table. This text file contains duplicate records. My question is how do you
tell mysql to delete duplicate records and load just the ones that are not
duplicate? I already tried the REPLACE and IGNORE key words but is I dont
get the result that I want.
Any idea

Thanks in advanced.
Nato


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php