With the project I'm on, I'll need to dedup some tables, so I decided I'd answer:
and start to learn how to dedup things in the Oracle environment.
I prefer (more) portable solutions, so I figured to create a temp table, populate it with the rows to dedup and join back to the original table to do a delete. In SQL Server, I would have done this to delete the duplicate rows:
However, Oracle didn't care for that at all and, after reading up on the DELETE statement, I finally settled on using a correlated subquery:
But JOIN is such a useful thing and one of the things I routinely do is steal JOIN clauses from other statements and paste them into new code. Using the correlated subquery, expecially with EXISTS and, perhaps, adverse performance implications, strikes me as awkward.
Is JOIN really not allowed in DELETEs? If not, what am I missing?
If so, is there a better way to qualify rows on the DELETE than the correlated subquery?
I imagine there's a way to do my DELETE that uses explicit or implicit cursors but I didn't really explore that.
I think that's the case, I also think that is an ANSI standard syntax.
I don't think there is. The optimizer is quite good about rewriting subqueries to be efficient, however I appreciate the point about wanting to steal queries to paste into deletes. I'm afraid Oracle just doesn't allow an explicit join syntax.
The subquery uses an implicit cursor, because that's the way Oracle works.
answered Feb 01 '10 at 06:43 AM