Re: sql question

ministry of education (moe003@netvision.net.il) wrote: : I have a table with multiple exact duplicate rows.No indexes. How do I : delete all duplicate rows to get a single occurrence for each row? Hmmmm...I have a table with multiple almost-but-not-quite exact duplicate rows--luckily, there's a field which is essentially 'time of entry' which is always at least a second different, so I make a table which includes a count of duplicate records and the maximum entry time, and then delete all the records which have a different entry time from the max. If they really are exact duplicates, I'd suggest something like this: * Create a table where you select everything from the table in question, plus a column which is the count of records. Something like create table duplicates as select *,dup_count=count(key_field) from orig_table group by key_field (I don't know if the '*,...' syntax would work--you might have to list all the field names.) * Delete all the duplicates from the original table. delete from orig_table a where a.key_field in (select key_field from duplicates d where dup_count > 1) and (whatever other equality provision you need to narrow things down) * Then insert records from the 'duplicates' table back into the original table, leaving out the dup_count column. (I forget the insert syntax, but you get the idea.) Try this on a test area first, in case I'm terribly wrong. But something like this should work. I'd say create an exact duplicate of the table in question, test it on that , and see if it worked. Then drop the table you created and do it on the real thing. Robert Slaven Department of Education, Culture and Employment Government of the Northwest Territories, Canada