From: Date: September 13 2006 6:28pm Subject: Re: query to find duplicate rows List-Archive: http://lists.mysql.com/mysql/201842 Message-Id: <005b01c6d751$af3e5b20$1502a8c0@BrentWD> MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit You should always have a field that is a unique id for the record (i.e. autoincrement). It makes it easier for differentiating and deleting duplicates if needed. This query only works if you have a unique id field and it will only delete one duplicate record at a time. So if you have 4 records that are duplicates, you'll need to run it 3 times. DELETE tablename FROM tablename, (SELECT MAX(uid) AS dupid,COUNT(uid) AS dupcnt FROM tablename GROUP BY id,url HAVING dupcnt>1) AS dups WHERE tablename.uid=dups.dupid; Here is an example: uid id url 1 20 google.com 2 25 yahoo.com 3 20 google.com 4 25 yahoo.com 5 20 google.com The above query would delete uid 4 and 5 on the first run and ui 3 on the second run. ----- Original Message ----- From: "Peter Van Dijck" To: "MYSQL General List" Sent: Tuesday, September 12, 2006 7:10 PM Subject: query to find duplicate rows > Hi all, a though query problem for me... > > I have a table with 2 rows that matter: url and id > > If url and id are the same in 2 rows, then that's no good (bad data). > > I need to find all the rows that are duplicates. I can't think of how > to approach the sql for this.. any pointers? > > Thanks! > Peter > > -- > find videoblogs: http://mefeedia.com > my blog: http://poorbuthappy.com/ease/ > my job: http://petervandijck.net > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=brenttech@stripped >