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:
DELETE mt FROM mytable mt inner join mytabledups mtd ON mt.col1 = mtd.col1 AND mt.col2 = mtd.col2 -- and the rest of the columns in both tables ;
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:
DELETE FROM mytable mt WHERE EXISTS (SELECT * FROM mytabledups mtd WHERE mt.col1 = mtd.col1 AND mt.col2 = mtd.col2) -- and the rest of the columns in both tables ;
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.