That's assuming that there is a unique identifier field, like an auto increment field. Although that could be added after the fact. Also, you need to run the query multiple times until it returns no affected records. So if there are 4 copies of a record, it would need to be run 3 times to get rid of all the dups. But I agree, that is the best way to remove duplicates in place provided the table is not too large.
Brent Baisley On Tue, Jul 14, 2009 at 11:52 AM, Marcus Bointon<mar...@synchromedia.co.uk> wrote: > You can combine the two queries you have in option 3 (you'll need to change > field names, but you should get the idea), something like this: > DELETE table1 FROM table1, (SELECT MAX(id) AS dupid, COUNT(id) AS dupcnt > FROM table1 WHERE field1 IS NOT NULL GROUP BY link_id HAVING dupcnt>1) AS > dups WHERE table1.id=dups.dupid; > Marcus > -- > Marcus Bointon > Synchromedia Limited: Creators of http://www.smartmessages.net/ > UK resellers of i...@hand CRM solutions > mar...@synchromedia.co.uk | http://www.synchromedia.co.uk/ > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
