RE: Logical duplicates

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 28 Jun 2013 08:20:56 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901409175_at_exmbx06.thus.corp>

Do you care how long it takes, and which one of the two routes is deleted. If not a simple solution would be:

delete from routes r1
where

        r1.dest1 < r1.dest2
and     exists (
        select null from routes r2
        where r2.dest1 = r1.dest2
        and      r2.dest2 = r1.dest1
        )

;

The first predicate ensures that you don't consider both (New York, Paris) and (Paris, New York) The second predicate checks for the existence of the same route in reverse order

I think I'd expect to see a hash anti-join for this - though if it's the wrong version of Oracle and your destinations are not declared NOT NULL then the optimizer might do something nasty.

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of rajugaru.vij_at_gmail.com [rajugaru.vij_at_gmail.com] Sent: 28 June 2013 09:06
To: oracle-l_at_freelists.org
Subject: Logical duplicates

Hi,
I have a table, called travel, with columns (source,destination,distance) this is something like, New york to paris and distance between them.

Logically New York to paris or Paris to New York both are same in distance.

But I have two entries for that, I waant to delete all such records.

Any suggestions?

Thanks
Sent on my BlackBerry® from Vodafone--
http://www.freelists.org/webpage/oracle-l-- http://www.freelists.org/webpage/oracle-l Received on Fri Jun 28 2013 - 10:20:56 CEST