| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Delete query must be run multiple times to remove all rows
A copy of this was sent to matthew.deal_at_cox.com
(if that email address didn't require changing)
On Thu, 06 Aug 1998 12:21:29 GMT, you 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'?
>
You needed to run it >1 time because of the way it was written. If you had 3 rows with the same values, only 1 of them could get deleted (select min(rowid) will return 1 rowid for that set of values). If you had three rows with the same values -- you needed to run the delete 3x's before you would get "No Rows deleted".
Another way to do this, in one pass, would have been:
delete from mytable
where rowid <> ( select max(rowid)
from mytable B
where b.col1 = mytable.col1
and b.col2 = mytable.col2
.... );
That would have scanned the full table and for each row in that table, decided if -- for that set of {col1, ... col11} -- this row had the 'maximum' rowid -- if not, delete it. For a single row with a unique value of {col1, ... col11} it would keep it and for other rows with duplicate {col1, ... col11} values it would keep it only if the rowid was the 'max' rowid for that set.
>Thanks!
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Aug 06 1998 - 09:15:23 CDT
 |
 |