Re: sql to delete rows from joining a table to itself
From: Ed Prochak <edprochak_at_gmail.com>
Date: Tue, 30 Jun 2009 17:14:11 -0700 (PDT)
Message-ID: <ba088c29-2fbc-4863-bc6c-ce7880c6ed3f_at_c36g2000yqn.googlegroups.com>
On Jun 29, 12:41 pm, Jeff Calico <jeffcal..._at_gmail.com> wrote:
> On Jun 29, 7:03 am, Ed Prochak <edproc..._at_gmail.com> wrote:
>
>
>
> > On Jun 28, 7:51 pm, Jeff Calico <jeffcal..._at_gmail.com> wrote:
>
> > > Hello everyone
>
> > > I am trying to delete rows from a massive data table that are "almost"
> > > duplicates. I can do a nice query
> > > to find them when I join the table with itself, but I can't figure out
> > > how to form a delete statement out
> > > of this.
>
> > > Query:
>
> > > SELECT a.*
> > > FROM Trafficdata a, Trafficdata b
> > > WHERE a.id = b.id
> > > AND a.date = b.date
> > > AND a.flag = 'Q'
> > > AND b.flag = 'Q'
> > > AND a.dstop = b.dstop
> > > AND (a.batch != b.batch OR (a.batch = b.batch AND a.pos_marker <
> > > b.pos_marker) );
>
> > > One idea I tried after searching the web was
> > > DELETE FROM ( <above query> ); but that gives
> > > ORA-01752 "Cannot delete from view without exactly one key preserved
> > > table"
>
> > > Any help would be greatly appreciated!
> > > Jeff C.
>
> > Do you have a primary key on this Trafficdata table?
> > Assuming your results are the rows your really want to delete then a
> > delete of the form:
>
> > DELETE FROM Trafficdata WHERE pk IN (SELECT a.pk from ...) ;
>
> > I suggest you do this AFTER doing a backup.
>
> > Ed
>
> Thanks for your reply. No, there isn't don't have a primary key on
> this data table. I also can't make a backup of the TrafficData table,
> due to its huge size, but I did make a smaller table to experiment on.
>
> Jeff
Date: Tue, 30 Jun 2009 17:14:11 -0700 (PDT)
Message-ID: <ba088c29-2fbc-4863-bc6c-ce7880c6ed3f_at_c36g2000yqn.googlegroups.com>
On Jun 29, 12:41 pm, Jeff Calico <jeffcal..._at_gmail.com> wrote:
> On Jun 29, 7:03 am, Ed Prochak <edproc..._at_gmail.com> wrote:
>
>
>
> > On Jun 28, 7:51 pm, Jeff Calico <jeffcal..._at_gmail.com> wrote:
>
> > > Hello everyone
>
> > > I am trying to delete rows from a massive data table that are "almost"
> > > duplicates. I can do a nice query
> > > to find them when I join the table with itself, but I can't figure out
> > > how to form a delete statement out
> > > of this.
>
> > > Query:
>
> > > SELECT a.*
> > > FROM Trafficdata a, Trafficdata b
> > > WHERE a.id = b.id
> > > AND a.date = b.date
> > > AND a.flag = 'Q'
> > > AND b.flag = 'Q'
> > > AND a.dstop = b.dstop
> > > AND (a.batch != b.batch OR (a.batch = b.batch AND a.pos_marker <
> > > b.pos_marker) );
>
> > > One idea I tried after searching the web was
> > > DELETE FROM ( <above query> ); but that gives
> > > ORA-01752 "Cannot delete from view without exactly one key preserved
> > > table"
>
> > > Any help would be greatly appreciated!
> > > Jeff C.
>
> > Do you have a primary key on this Trafficdata table?
> > Assuming your results are the rows your really want to delete then a
> > delete of the form:
>
> > DELETE FROM Trafficdata WHERE pk IN (SELECT a.pk from ...) ;
>
> > I suggest you do this AFTER doing a backup.
>
> > Ed
>
> Thanks for your reply. No, there isn't don't have a primary key on
> this data table. I also can't make a backup of the TrafficData table,
> due to its huge size, but I did make a smaller table to experiment on.
>
> Jeff
As you saw, ROWID works for this case.
But about backup, you are saying you (a developer) cannot back up the table, right? I meant for the DBA to do a backup before changing a table that is so massive. (And if the DBA cannot do the backup you have bigger problems than duplicates!)
Good luck.
Ed
Received on Tue Jun 30 2009 - 19:14:11 CDT