July 29, 2015 at 5:49 pm
I am currently using 5 separate CTEs and I am wondering if I can use only 1 cte. What I am
listing below is what 2 ctes looks like. Thus can you tell me if I can use the same cte that
is listed in the sql below? If so, would you show me how to change the sql listed below to
use only 1 cte?
Merge TST.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo2
from
[LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber =
LKR.number
JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID
JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID
and LKC.seq = 1
) AS LKC2 (lockID,seq,combo)
ON
(
LKC1.lockID = LKC2.lockID
and LKC1.seq = 1 and LKC2.seq =2
)
WHEN NOT MATCHED
THEN INSERT (lockID,seq,combo) VALUES(LKC2.lockID,2,LKC2.combo)
WHEN MATCHED
THEN UPDATE SET LKC1.combo = LKC2.combo;
----
Merge TST.dbo.LockCombination AS LKC1
USING
(select LKC.lockID,LKC.seq,A.lockCombo3
from
[LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber =
LKR.number
JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID
JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID
and LKC.seq = 1
) AS LKC2 (lockID,seq,combo)
ON
(
LKC1.lockID = LKC2.lockID
and LKC1.seq = 1 and LKC2.seq =3
)
WHEN NOT MATCHED
THEN INSERT (lockID,seq,combo) VALUES(LKC2.lockID,3,LKC2.combo)
WHEN MATCHED
THEN UPDATE SET LKC1.combo = LKC2.combo;
July 30, 2015 at 1:08 am
Don't CTEs have to begin with the keyword 'WITH'?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 30, 2015 at 4:18 am
Next, looks like this subquery will always return rows with LKC2.seq == 1, if any.
(select LKC.lockID,LKC.seq,A.lockCombo2
from
[LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber =
LKR.number
JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID
JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID
and LKC.seq = 1
) AS LKC2 (lockID,seq,combo)
So MATCHED will never happen in
MERGE ON .. AND LKC2.seq = 2
And the query may be refactored to plain INSERT, why you need MERGE at all?
July 30, 2015 at 4:30 am
I'm seeing two different MERGE commands, no CTEs in sight.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply