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

From: Ed Prochak <edprochak_at_gmail.com>
Date: Mon, 29 Jun 2009 05:03:00 -0700 (PDT)
Message-ID: <d8d5598e-d98b-471f-9410-0a042c7880a5_at_b9g2000yqm.googlegroups.com>

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 Received on Mon Jun 29 2009 - 07:03:00 CDT