October 3rd, 2000, 06:04 PM
I have a MySQL table that looks something like this:
id url name
1 http://www.yahoo.com Yahoo
2 http://www.aol.com AOL
3 http://www.excite.com Excite
4 http://www.yahoo.com YAHOO!
5 http://www.yahoo.com/abc Yahoo!
Can someone give me the Perl code that could go through the MySQL table and remove entries with duplicate URLs? No consideration should be given to name. For this example 1 and 4 should be considered duplicates. 4 should be removed and 1 should stay. 6 should not be regarded as a duplicate.
I appreciate the help!
[This message has been edited by scream (edited October 03, 2000).]
October 3rd, 2000, 07:32 PM
Well you should have made the field unique in the 1st place. The easiest way (if it works) to fix it is to alter the table and make the field a unique key.
If that doesn't work you'll have to do some working with arrays/hashs and get it fixed.
October 4th, 2000, 11:56 PM
what would happen if you used a query to create a new table with just distinct entries?
mysql>CREATE distinct_urls SELECT id, url, name
->GROUP BY url;
That query would create a new table with just the distinct URL's, an id from one of them, an a name from one of them. I don't know if you could control whether or not 1 became 4, or 4 became 1. At any rate, it's worth a try.
October 10th, 2000, 10:24 PM
Thanks for the input. Your suggestions allowed me to accomplish what I was hoping for!