Re: Deleting Duplicate Records

2005-11-08 Thread 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]
 mailto:[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

Re: Deleting Duplicate Records

2005-11-08 Thread 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

2003-07-31 Thread 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

2003-07-31 Thread 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

2003-07-31 Thread 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

2003-07-31 Thread 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

2003-07-30 Thread 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]

Re: Deleting Duplicate Records

2003-07-30 Thread 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

2002-03-05 Thread 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

2002-03-04 Thread 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

Re: Deleting duplicate records

2002-03-04 Thread [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

2002-03-04 Thread 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

2002-03-04 Thread 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