Delete query must be run multiple times to remove all rows

Hi!

I needed to delete rows of data from a table which had many of the same values in columns. These were duplicate data elements, but the primary key was different for some. In short, the same rows were added to the table with slighly different primary key values. Thus, they were the same, but the primary key constraint was not violated because the primary key values were different. I formulated the following query to perform the task:

delete from mytable
where rowid in (
SELECT min(rowid)
FROM mytable
GROUP BY col1, col2o, col3, col4, col5, col6, col7, col8, col9, col10, col11 HAVING COUNT (*) > 1; A select version of such query would produce 10,000 rows. This means I want to get rid of 10,000 rows which met my criteria. This was agreeable. There were a total of 50,000 rows in the table altogether. When I executed the above query it only deleted some of the rows. If I executed the above query again it deleted more rows. Execution of the query needed to be done multiple times to remove all rows meeting the criteria.

Why do I need to run this query multiple times to remove all of the rows which meet the criteria? Is is because I was using 'rowid'?

Thanks!

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Aug 06 1998 - 07:21:29 CDT