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
