sql to delete rows from joining a table to itself

From: Jeff Calico <jeffcalico_at_gmail.com>
Date: Sun, 28 Jun 2009 16:51:42 -0700 (PDT)
Message-ID: <84f268e5-2093-484e-89c5-04f593e0be8c_at_m19g2000yqk.googlegroups.com>

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. Received on Sun Jun 28 2009 - 18:51:42 CDT