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]
