question

KillerDBA avatar image
KillerDBA asked

Can I use JOIN to qualify rows for a DELETE? The "S" in "SQL" isn't for "Standard," is it?

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:

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.

sqloracledeletejoin
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Andrew Mobbs avatar image
Andrew Mobbs answered

Is JOIN really not allowed in DELETEs?

I think that's the case, I also think that is an ANSI standard syntax.

If so, is there a better way to qualify rows on the DELETE than the correlated subquery?

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.

I imagine there's a way to do my DELETE that uses explicit or implicit cursors but I didn't really explore that.

The subquery uses an implicit cursor, because that's the way Oracle works.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KillerDBA avatar image KillerDBA commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.