November 12, 2016 at 5:02 am
Hi All!
I got some code from another, where i have som understanding problems.
Its pretty long, but the start is:
DELETE FROM dbo.DutyRosterShift FROM dbo.DutyRosterShift B
WHERE
B.DutyRosterId = 513 AND ....
What follows WHERE uses some different tables to find the posts.
The query deletes some hundreds of posts.
What do not understand is why there is used this double FROM and what it exactly does.
Any, who will entlighten me?
Best regards
Edvard Korsbæk
November 12, 2016 at 5:19 am
Edvard Korsbæk (11/12/2016)
Hi All!I got some code from another, where i have som understanding problems.
Its pretty long, but the start is:
DELETE FROM dbo.DutyRosterShift FROM dbo.DutyRosterShift B
WHERE
B.DutyRosterId = 513 AND ....
What follows WHERE uses some different tables to find the posts.
The query deletes some hundreds of posts.
What do not understand is why there is used this double FROM and what it exactly does.
Any, who will entlighten me?
Best regards
Edvard Korsbæk
You can replace the first instance of FROM dbo.DutyRosterShift with the alias B, that's what the parser will do anyway.
😎
November 12, 2016 at 5:44 am
You can replace the first instance of FROM dbo.DutyRosterShift with the alias B, that's what the parser will do anyway.
😎
If I change to:
DELETE FROM dbo.DutyRosterShift B
WHERE
......
I get this errorcode:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'B'.
I remember something about, that a delete statement cannot take a name.
Best regards
Edvard Korsbæk
November 12, 2016 at 6:32 am
Edvard Korsbæk (11/12/2016)
You can replace the first instance of FROM dbo.DutyRosterShift with the alias B, that's what the parser will do anyway.
😎
If I change to:
DELETE FROM dbo.DutyRosterShift B
WHERE
......
I get this errorcode:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'B'.
I remember something about, that a delete statement cannot take a name.
Best regards
Edvard Korsbæk
DELETE
B
FROM dbo.DutyRosterShift B
WHERE
😎
November 12, 2016 at 7:57 am
Edvard Korsbæk (11/12/2016)
Hi All!I got some code from another, where i have som understanding problems.
Its pretty long, but the start is:
DELETE FROM dbo.DutyRosterShift FROM dbo.DutyRosterShift B
WHERE
B.DutyRosterId = 513 AND ....
What follows WHERE uses some different tables to find the posts.
The query deletes some hundreds of posts.
What do not understand is why there is used this double FROM and what it exactly does.
Any, who will entlighten me?
Best regards
Edvard Korsbæk
The reason there are two FROMs in DELETE is to support JOINs. The first FROM identifies the table you want to DELETE from. The second FROM is for joins that act as criteria for the DELETE. If there are no joins, the there is no need for the second FROM. Using the word FROM for the first FROM is totally optional just like the INTO for INSERT is totally optional.
There are 3 basic forms of DELETE that I follow...
[font="Arial Black"]1. Delete with no joins ...[/font]
DELETE {FROM} schemaname.deltablename
WHERE someconditionexists
;
I say "no joins" but the "someconditionexists" can be a WHERE IN/NOT IN, WHERE EXISTS/NOT EXISTS, or other correlation/sub-query, which can act as a join. I put the FROM in braces because it's not required. For this form, I usually do include it just for obvious readability. You MUST be careful when using this form IAW correlations other than {NOT} EXISTS/{NOT}IN. Please see the 4th form further below for what I mean.
[font="Arial Black"]2. Delete based on joins...[/font]
DELETE {FROM} schemaname.deltablename
FROM schemaname.deltablename tgt
JOIN schemaname.othtablename oth
ON somejoinconditionexists
{WHERE somerestrictiveconditionexists}
;
In order for the DELETE to be properly formed during a join, the table that you're deleting from MUST be listed twice... once as the Target (tgt) of the DELETE and once for the join. The WHERE clause is optional.
[font="Arial Black"]3. Delete based on joins using an alias...[/font]
DELETE {FROM} tgt
FROM schemaname.deltablename tgt
JOIN schemaname.othtablename oth
ON somejoinconditionexists
{WHERE somerestrictiveconditionexists}
;
The form above is what I usually use for joined deletes. It's nearly identical to form #2 above except the first FROM simply uses the alias for the Target table from the second from instead of listing the table name twice. I don't know about lately but this method used to be a bit faster than form #2 above.
[font="Arial Black"]4. There IS a rule that you need to follow for both DELETEs and UPDATEs [/font]and that is, [font="Arial Black"]if there's a join, the Target table MUST appear in the 2nd FROM clause! [/font]
For example, you will never find the following form of a DELETE in "Books Online" or any other official MS documentation on the syntax of the DELETE statement...
DELETE {FROM} schemaname.deltablename tgt
FROM schemaname.othtablename oth
WHERE tgt.SomeColumn = oth.SomeColumn
;
While it frequently works just fine, it will sometimes not spool correctly and it will allow "Halloweening" to occur in a most unpredictable fashion. If that happens (and it eventually will as the tables grow), a normally fast DELETE (say a second or two) will slam several CPUs against the wall and take up to several hours to complete (voice of experience several times there and have helped others fix it many times on these good forums).
I've not taken the time to do a deep analysis of the problems associated with form #4 above because people are normally in a dead panic for a resolution when it occurs and, owning to the sporadic nature of when it occurs, is a bit difficult to setup a predictable test for. There are a lot of variables to it. For example, we had identical copies of the databases on a "test" machine but the machine wasn't identical to the prod box (fewer CPUs, for example). The code worked against the identical data on the test machine and we couldn't make it go into the "Halloweening" mode. It would always "Halloween" on the production box, though (for that one particular case).
With that in mind, I just avoid the 4th form in my code and fix it when I see it in someone else's code.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 12, 2016 at 10:48 am
Thanks both!
This makes sense, and makes my 'Deletes' much easier. They have always been a bit difficult.
Now i just need to understand, why you use so much time om me....:-)
Best and thankfull regards!
Edvard
November 12, 2016 at 11:15 am
Edvard Korsbæk (11/12/2016)
Now i just need to understand, why you use so much time om me....:-)
You've already answered that question. 😀
Edvard Korsbæk (11/12/2016)
This makes sense, and makes my 'Deletes' much easier. They have always been a bit difficult.
To borrow a phrase from the master of duct tape, "We're all in this together and I'm pullin' for ya". 🙂
Thank you for the feedback and glad we could help.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply