Re: Delete query must be run multiple times to remove all rows

matthew.deal_at_cox.com wrote:
>
> 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

hi matthew,

no its because of your min(rowid)
example:

id col1 col2 col3

1       1       1       1
2       1       1       1
3       1       1       1

you will only delete the row with id = 1 with your query. running it again will delete row with id = 2.

--
cu maxx

to reply just remove the no-spam. from my address Received on Fri Aug 07 1998 - 06:02:22 CDT