August 15, 2014 at 5:57 am
Hi all!
I have inherited a query, which does exactly what i want. Only problem is, that I really do not understand why.
The first lines are:
DELETE FROM dbo.DutyRosterShift
FROM dbo.DutyRosterShift B
WHERE
(Lot of conditions)
I really do not understand why it's not
DELETE FROM dbo.dutyrostershift
Where
(same conditions)
Any who can give me a little more understanding:-)?
Best regards
Edvard Korsbæk
The full query is:
DELETE FROM dbo.DutyRosterShift
FROM dbo.DutyRosterShift B
WHERE
B.DutyRosterId = 238 AND ((B.EmployeeId = 393 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 393 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 393 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 393 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 394 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 394 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 394 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 394 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 394 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 395 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 395 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 395 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 395 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 395 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 396 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 396 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 396 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 396 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 396 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 430 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 430 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 430 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 430 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 397 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 397 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 397 AND B.EmployeeGroupId = 42) OR (B.EmployeeId = 397 AND B.EmployeeGroupId = 42)) AND B.Dato BETWEEN '20140801' AND '20140831' AND NOT EXISTS (SELECT D.Id FROM dbo.DutyRosterShift D WHERE D.DutyRosterId = 238 AND D.EmployeeGroupId = B.EmployeeGroupId AND D.EmployeeId = B.EmployeeId AND D.Dato = B.Dato AND D.StatusNo >= 4) AND (B.ShiftType = 0 OR B.ShiftType = 2 OR B.ShiftType = 3 OR B.ShiftType = 4 OR B.ShiftType = 5 OR B.ShiftType = 6 OR B.ShiftType = 7 OR B.ShiftType = 11 OR B.ShiftType = 13 OR B.ShiftType = 15 OR B.ShiftType = 16 OR B.ShiftType = 17 OR B.ShiftType = 18 OR B.ShiftType = 19 OR B.ShiftType = 20 OR B.ShiftType = 22 OR B.ShiftType = 23 OR B.ShiftType = 24 OR B.ShiftType = 39 OR B.ShiftType = 40 OR B.ShiftType = 41 OR B.ShiftType = 42)
August 15, 2014 at 6:23 am
It's all detailed in Books Online. Basically, the below query is aliasing a source table for the deletes and is designed to avoid a subquery.
DELETE FROM dbo.DutyRosterShift
FROM dbo.DutyRosterShift B
WHERE
(Lot of conditions)
Just so happens the user is aliasing the same table. A straight and simple delete would work here
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 15, 2014 at 6:26 am
Thanks!
Can you give me a link to 'Detailed in BOOK ONLINE'?
Best regards
Edvard Korsbæk
August 15, 2014 at 6:35 am
This link http://technet.microsoft.com/en-us/library/ms189835.aspx
Details
SQL Server Books Online
FROM table_sourceSpecifies an additional FROM clause. This Transact-SQL extension to DELETE allows specifying data from <table_source> and deleting the corresponding rows from the table in the first FROM clause.
This extension, specifying a join, can be used instead of a subquery in the WHERE clause to identify rows to be removed.
For more information, see FROM (Transact-SQL).
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 15, 2014 at 6:36 am
A simple search in Bing gave the first result as http://msdn.microsoft.com/en-us/library/ms189835%28v=sql.110%29.aspx.
August 15, 2014 at 6:51 am
That's a scarry piece of T-SQL code. I'm guessing it's auto generated by an ORM tool or something. First, let's use a code formatting tool to untangle it. To produce the following, I used a SSMS plugin called "Poor Man's T-SQL Formatter".
http://www.architectshack.com/PoorMansTSqlFormatter.ashx
delete
from dbo.DutyRosterShift
from dbo.DutyRosterShift B
where B.DutyRosterId = 238
and (
(
B.EmployeeId = 393
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 393
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 393
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 393
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 394
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 394
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 394
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 394
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 394
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 395
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 395
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 395
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 395
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 395
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 396
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 396
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 396
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 396
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 396
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 430
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 430
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 430
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 430
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 397
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 397
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 397
and B.EmployeeGroupId = 42
)
or (
B.EmployeeId = 397
and B.EmployeeGroupId = 42
)
)
and B.Dato between '20140801'
and '20140831'
and not exists (
select D.Id
from dbo.DutyRosterShift D
where D.DutyRosterId = 238
and D.EmployeeGroupId = B.EmployeeGroupId
and D.EmployeeId = B.EmployeeId
and D.Dato = B.Dato
and D.StatusNo >= 4
)
and (
B.ShiftType = 0
or B.ShiftType = 2
or B.ShiftType = 3
or B.ShiftType = 4
or B.ShiftType = 5
or B.ShiftType = 6
or B.ShiftType = 7
or B.ShiftType = 11
or B.ShiftType = 13
or B.ShiftType = 15
or B.ShiftType = 16
or B.ShiftType = 17
or B.ShiftType = 18
or B.ShiftType = 19
or B.ShiftType = 20
or B.ShiftType = 22
or B.ShiftType = 23
or B.ShiftType = 24
or B.ShiftType = 39
or B.ShiftType = 40
or B.ShiftType = 41
or B.ShiftType = 42
)
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply