Re: Delete of mirrored rows

On Nov 6, 8:14 pm, Jeff Kish <jeff.k..._at_mro.com> wrote:
> hi.
> I'm targeting oracle 9 onwards.
>
> I've seen ways to delete duplicate rows.
> Can someone give me some sql to do this?
>
> I have a table with varchar2 table_name_start, varchar2 column_name,
> varchar2 table_name_end;
>
> it has rows like this:
> table1 col1 table2
> table1 col2 table 3
> table2 col1 table1
>
> There are not be any completely duplicate rows, or any null values.
>
> I'd lke to delete the rows if they exist with the names swapped
> around, i.e. like above since the first and third share a column name
> and the table_name_start/end matches the others table_name_end/start,
> I'd like to delete one and leave the other.
>
> I'm scratching my head trying to figure this out.
>
> I had a solution for ms sql servfer 2000, but I can't seem to figure out how
> to make Oracle digest the same sql...
>
> (humble thanks to David Portas)
> DELETE FROM tbl
> WHERE EXISTS
> (SELECT *
> FROM tbl AS T
> WHERE T.table_name_start = tbl.table_name_end
> AND T.table_name_end = tbl.table_name_start
> AND T.column_name = T.column_name)
> AND table_name_start > table_name_end ;
> Jeff Kish

I have recorded two methods of performing the delete that I use to job my memory, which sometimes takes the day off. Her is one:

--
 delete from table_name
 where (key_list, rowid) in
       ( select  keys, rowid from table_name
         minus
         select  keys, min(rowid) from table_name
         group by keys
       )
 /

HTH -- Mark D Powell --