Re: Delete of mirrored rows

On 6 Nov 2006 17:24:25 -0800, "Mark D Powell" <Mark.Powell_at_eds.com> wrote:

>
>
>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:
Thanks Mark. I'll check it out.

Regards,
Jeff Kish Received on Mon Nov 06 2006 - 20:26:41 CST