Re: sql to delete rows from joining a table to itself

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 29 Jun 2009 06:27:49 -0700 (PDT)
Message-ID: <97289b90-0178-4f32-afbd-608235f4ae35_at_l32g2000vbp.googlegroups.com>



On Jun 29, 8: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- Hide quoted text -
>
> - Show quoted text -

Assuming you want to keep one copy of each row the following will work

delete from table_name
  where (key_list, rowid) in

        ( select  keys, rowid from table_name
          minus
          select  keys, min(rowid) from table_name
          group by keys
        )

/

HTH -- Mark D Powell -- Received on Mon Jun 29 2009 - 08:27:49 CDT

Original text of this message