x

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.

more ▼

asked Jan 29, 2010 at 02:46 PM in Default

KillerDBA gravatar image

KillerDBA
1.5k 8 9 10

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Feb 01, 2010 at 06:43 AM

Andrew Mobbs gravatar image

Andrew Mobbs
1.5k 1 3

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, 2010 at 12:13 PM KillerDBA
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x729
x378
x52
x22

asked: Jan 29, 2010 at 02:46 PM

Seen: 4198 times

Last Updated: Feb 01, 2010 at 04:17 PM