Delete statement with joins

Question

Delete statement with joins

suedeuno

SSC Journeyman

Points: 79
More actions
November 30, 2009 at 7:43 am

#73410

Just wanted to make sure that if I did something like the following that I would only be deleting from the 'FROM' (or 'a') table and that it would not delete from the joined tables as well.
DELETE a FROM ATable a
INNER JOIN BTable b
INNER JOIN CTable c
WHERE b.Field = 'something'
AND c.Field = 'something else'

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

A delete can only affect a single table. It will affect the table that's specified straight after the DELETE [FROM]. Any tables after the second FROM are just used to identify rows to be deleted.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 2

What Gail has said is right, it would delete from only one table.

Your statement is missing ON clauses, is this only a test statement you have posted?

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 3

As Bru mentioned, you haven't joined TableA, so you might end up deleting everything in that table. Only one table is affected, but how it is affected depends on your query.

Write this as a SELECT, double check the results, and then turn it into a DELETE.

Garadin One Orange Chip Points: 29613 More actions · Answer 4

I personally nest all update/delete statements to real data in transactions as well, just to be extra cautious.

something like:

BEGIN TRAN

--Your Delete Statement

ROLLBACK TRAN

--COMMIT TRAN

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Steve Jones - Editor (11/30/2009)
As Bru mentioned, you haven't joined TableA

It is joined, just the ON clauses are missing

DELETE FROM a -- table to be deleted from

FROM ATable a -- join to other tables to filter

INNER JOIN BTable b .....