4Pics

Anyone have code or know how to delete duplicate rows in a larger database. I have about 55k rows and want to remove all the duplicate urls in it.

Thanks

brutus

/
dev nul

__________________

teksonline

backup first

if you know what your deleting

"hair in my cabbage" is all the data in that field for example

delete from <table> where <tableelement> LIKE 'hair in my cabbage'

backup first!!!!!!!!! dont blame me later

rickholio

I'd suggest that the easiest way to do it is through a temporary table... do a SELECT DISTINCT into the temp table to eliminate the duplicates.

Alternately, a cute way to do a de-facto culling is to ALTER TABLE and make your url column UNIQUE, ignoring (and therefore dropping) duplicates:

ALTER IGNORE TABLE sometable ADD UNIQUE (url)

... which would serve the additional purpose of permanently removing the possibility of duplicate URLs in the future, as well.

Obviously you'll want to back up everything before you try any of this.

SMG

I'd do something like this (but backup first)

$rs = mysql_query("SELECT id, url FROM your_table ORDER BY id ASC");
while (list($id,$url)=mysql_fetch_row($rs)) {
mysql_query("DELETE FROM your_table WHERE url = '$url' AND id != '$id'");
}

that will go through all your rows and delete all duplicates other than the first one ... you could change ASC to DESC to make it delete all but the newest ... obviously you have to probably change the field and table names though.

__________________
TGP Webmasters: sign up for the top 100 tgp list!
Submit galleries
If you add me to icq (title) make sure to mention GFY or I'll think you're a bot and deny you.

4Pics

Awesome, that alter thing was the easiest way!

thank you!

rickholio