Re: Remove Date overlap duplicates

From: CarlosAL <>
Date: Mon, 5 Oct 2009 00:31:09 -0700 (PDT)
Message-ID: <>

On Oct 5, 8:11 am, "Shank..." <> 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."


Carlos. Received on Mon Oct 05 2009 - 02:31:09 CDT

Original text of this message