Re: Help: Delete all rows except 1 of a set of rows.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 16 Sep 1998 19:08:48 GMT
Message-ID: <360f0b83.98779237@192.86.155.100>

A copy of this was sent to "Arjan van Bentem" <avbentem_at_DONT-YOU-DAREdds.nl> (if that email address didn't require changing) On Wed, 16 Sep 1998 20:54:26 +0200, you wrote:

>>delete from ri_result
>>where rowid <> ( select max(rowid)
>> from ri_result b
>> where b.risn = ri_result.risn
>> and b.crit_code = ri_result.crit_code
>> and b.crit_rev = ri_result.crit_rev )
>
>
>Hmmmm, does this really work? By not specifying an alias for ri_result in
>the first FROM, and give the alias B for the second occurence of ri_result,
>does Oracle understand that ri_result.risn, ri_result.crit_code and
>ri_result.crit_rev refer to the first table? If it does not, then only one
>row would be left after this update... I would use:
>
> delete from ri_result a /* note the alias a here */
> where rowid <>
> ( select max(rowid)
> from ri_result b
> where b.risn = a.risn /* note the references to a here */
> and b.crit_code = a.crit_code
> and b.crit_rev = a.crit_rev )
>
>Arjan.
>

hmm, no it works. when you give an alias to the table (B) in the first subquery, the table ri_result is 'hidden' in the subquery -- the alias reduces the scope of that tablename to that from clause. ri_result in my original delete is not ambigous -- it can ONLY refer to the outer table, there is no ri_result table in the subquery as far as SQL is concerned since we gave it a correlation name...

SQL> create table t1 ( x int );
Table created.

SQL> insert into t1 values ( 1 );
SQL> insert into t1 values ( 1 );
SQL> insert into t1 values ( 2 );
SQL> insert into t1 values ( 2 );
SQL> insert into t1 values ( 3 );

SQL> delete from t1
  2 where rowid <> ( select max(rowid)

  3                     from t1 b
  4                     where b.x = t1.x )

  5 /
2 rows deleted.

SQL> select * from t1;

         X

---

         1
         2
         3

>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

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 Wed Sep 16 1998 - 14:08:48 CDT