Thanks David! The entire row, not just one or two columns, is a duplicate which makes life fun.. :-)
I can key or unique index only a few columns once the data is cleaned up to prevent this problem. If I create an identical table and include either a key or unique index (innodb seems to like the index better) on all the columns and do a insert into new_table using select * from old_table will mysql quit inserting once a duplicate is hit, or will it keep going and skip over the duplicates? walt On Tuesday 30 July 2002 03:57 pm, David Kramer wrote: > You could always use an insert statement into a second table, when > performing the insert use a GROUP BY clause to consalidate your records > > something along these lines but this isnt 100% accurate, I would need the > table DDL and business rules behind the DEDUP > > Insert into tableB > ( > column names, ... > > > ) > (select > column a, > column b, > max(column c), --or you could use MIN > from > table A > > group by > column a, > column b); > > > > > > **JUST make sure your Identifing column, i.e. the column you use to tell > which is a duplicate record or not is included in the group by. Also what > are the business rules behind the DEDUP(Deduplication)? Are the other > values contained in the other columns necessary? If you tell me more about > what your trying to do and provide some Table DDL I can help you write this > query. Just let me know! > > Thanks, > > DK > > group by statement > > -----Original Message----- > From: walt [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, July 30, 2002 12:43 PM > To: [EMAIL PROTECTED] > Subject: removing duplicate records > > > Does anyone know a good way find and remove duplicate records from a table? > I can create an identical table structure and use a script to pull records > from the existing table and insert them into the new table if they are not > duplicates, but I'd rather not do it that way. Here is an example of an sql > script I use for Oracle databases > > delete from employee a > where rowid < ( > select max(rowid) > from employee b > where b.COL1 = a.COL1 > and b.COL2 = a.COL2 > and b.COL# = a.COL#); > > sql, query > > Thanks in advance! --------------------------------------------------------------------- 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
