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

>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. Received on Wed Sep 16 1998 - 13:54:26 CDT