Delete duplicate rows

Hello everybody!

I was testing the following correlated subquery for the table duplicateRowsTable (row_id, name):

delete duplicateRowsTable
where row_id > (
 select min(row_id)
 from duplicateRowsTable m
 where m.name = duplicateRowsTable.name
)

The contents of the table are:

row_id     name
-------     -----
1              A
2              B
3              C
4              A
5              B
6              C
7              A
8              B
9              C
10            A
11            B
12            C
13            D
14            E
15            D

The query works fine, deleting duplicate rows from the table. Still, I would like to know this: as the query is in progress and some duplicate rows have already been deleted from the table, each time the inner subquery is executed, how many records will there be in table m?
What I mean is if rows have been deleted from the table, the table m inside the subquery knows about it or not?
If not (as I believe), why?

Thanx.

PS. Please reply to gecon27_at_hotmail.com. Received on Sun May 20 2001 - 15:01:15 CEST