|
With the project I'm on, I'll need to dedup some tables, so I decided I'd answer: Question on Removing Duplicates 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.
(comments are locked)
|
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. Thanks for the answer. My use of terminology in my original question was poor. I know that Oracle uses an implicit cursor for everything. When I wrote "implicit cursors" I meant those quickie cursors with the compact open cursor and for loop syntax all in one (I like that feature). I can't recall what they're called, though. Anyway, thanks for confirming my guess. I also just skimmed through the '92 standard and DELETE with JOIN doesn't appear to be in there! I'm bummed. And after some thought, I think maybe I'll just embrace the cursor and try that next time.
Feb 01 '10 at 12:13 PM
KillerDBA
(comments are locked)
|

