Re: Remove Date overlap duplicates
From: CarlosAL <miotromailcarlos_at_netscape.net>
Date: Mon, 5 Oct 2009 00:31:09 -0700 (PDT)
Message-ID: <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?
Date: Mon, 5 Oct 2009 00:31:09 -0700 (PDT)
Message-ID: <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. Received on Mon Oct 05 2009 - 02:31:09 CDT