Using join in delete operation

Question

Using join in delete operation

Him Acharya

SSC Enthusiast

Points: 180
More actions
November 12, 2007 at 11:14 pm

#61402

I saw a script as
DELETE FROM #tmp_searched_table1
FROM #tmp_searched_table1 tsd, table_2 dta
WHERE dta.doc_id=tsd.doc_id_pk
And this script worked fast then the normal script. I'm using the script but what actually happens in this script? Two 'FROM ' are used for the same table. Can any one tell me about this feature of MS Sql .

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

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

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

I'd only be regurgitating what's in Books Online... recommend you lookup "DELETE" in Books Online for a full explanation of SQL Server's proprietary and nasty fast DELETE. You might also want to lookup UPDATE since it is also quite different from most RDBMS's.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Greg Snidow SSCoach Points: 16108 More actions · Answer 2

BOL does list that you can have two from clauses in a delete statement, but it does not really say why, at least not in English. It is kind of annoying, as I like to do a select statement first to see what I am about to delete, then just replace select with delete. The fact that delete allows two froms where select does not makes me think I may be missing something. Any ideas?

Greg

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

I always used the

Delete tableA from

tableA inner join TableB on tablea.ida=tableb.idb

kind of syntax. Two FROM's? that's confusing.... Much more readable if you skip the first FROM, IMO.

Greg - it DOES explain why. It's just hard to make an explanation of a confusing syntax not also be confusing:) Note that this link has TWO listings of FROM, with a different explanation...

http://msdn2.microsoft.com/en-us/library/ms189835.aspx

the first "from" is to denote which table you're deleting from, then second "from" is the standard FROM clause of a T-SQL select/insert/update statement.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Antares686 SSC Guru Points: 125444 More actions · Answer 4

My statement is think of everything after the from as developing a set of data from the table you are deleteing from. If you do SELECT TableName.* instead you see the output. All that the from does is limit the set of items to remove from the table as opposed to ALL.

Greg Snidow SSCoach Points: 16108 More actions · Answer 5

Matt Miller (11/13/2007)
Greg - it DOES explain why. It's just hard to make an explanation of a confusing syntax not also be confusing:) Note that this link has TWO listings of FROM, with a different explanation...

Yup...not really English enough, but I get the just of it...I guess.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

Sergiy SSC Guru Points: 110209 More actions · Answer 6

DELETE tsd

FROM #tmp_searched_table1 tsd, table_2 dta

WHERE dta.doc_id=tsd.doc_id_pk

_____________
Code for TallyGenerator