Re: SELECT 'X'

smontgomerie_at_hotmail.com (Steve) wrote in message news:<156709aa.0208140557.2815ff3c_at_posting.google.com>...
> tks
>
> here's a better example
>
> delete from my_table t1
> where exists (select 'x' from my_table t2
> where t2.deptno = t1.deptno
> and t2.dname = t1.dname
> and t2.rowid > t1.rowid);
>
> the delete removes duplicates from a table
>
> so in this case is 'X' like a cursor, cheking for all records
> tht meet the criteria and then subsequently passing the records
> to the delete for removal?
>
> steve
>

The closest thing that _might_ work (didn't want to test it mysel, read on why) is:
delete from my_table T1
where 1< (select count(*) from mytable t2

          where t2.deptno = t1.deptno
          and t2.dname = t1.dname
          and t2.rowid > t1.rowid)

But be carefull: this will delete ALL records that exist multiple times. If a record (deptno-dname combination) exists 3 times, _three_ rcords (and not two) will be deleted (if this peace of **** actually works that is)
I guess this is not what you want. So, go read the site. Received on Fri Aug 23 2002 - 15:14:43 CEST