Re: Remove Date overlap duplicates

"CarlosAL" <miotromailcarlos_at_netscape.net> a écrit dans le message de news: 082a11cd-e9b2-43c4-a883-4f2a5f00e47d_at_p23g2000vbl.googlegroups.com... On Oct 5, 8:11 am, "Shank..." <willbecrea..._at_gmail.com> wrote:
> Hi,
>
> I have a table containing following data.
>
> ID Start_date End_date
> 1 20-OCT-2005 31-DEC-2005
> 1 01-NOV-2005 31-JAN-2006
> 2 01-JAN-2005 31-JAN-2005
> 2 01-JAN-2005 31-DEC-2006
>
> Now I need to update the start date of 2nd Row as '01-jan-2006' and I
> also need to delete the 3rd row.
> In a nut shell, I need to remove the duplicate data either by updating
> or by deleting.
> Is there any way to do it using PL/SQL?

You may use OVERLAPS predicate, which is an ANSI SQL standard used in others RDBMS (Teradata, for example). It is in Oracle (undocumented though) since version 10(?).

Even, there is an Oracle ERROR for it:

"ORA-30085: syntax error was found in overlaps predicate

    Cause: A syntax error was found during parsing an overlaps predicate."

Cheers.

Carlos.

http://www.orafaq.com/node/2067

Regards
Michel Received on Mon Oct 05 2009 - 10:47:02 CDT