July 29, 2015 at 5:48 pm
In the following t-sql 2012 merge statement, the insert statement works but the update
statement does not work. I know that is true since I looked at the results of the update
statement:
Merge TST.dbo.LockCombination AS LKC1
USING
(select LKC.comboID,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 (comboID,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;
When I execute the following update statement but itself alone, the update statement works:
UPDATE LKC
SET LKC.combo = 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 OPS.dbo.Lock LK ON LKR.lockID = LK.lockID
JOIN OPS.dbo.LockCombination LKC ON LK.lockID = LKC.lockID
WHERE LKC.seq = 2
Thus can you show me some t-sql 2012 that I can use to male update statement work in the
merge function?
July 30, 2015 at 9:25 am
inside your subquery, it looks like you are limitting the "LKC.seq" to 1 in order for LockCombination to be joined in (and thus LKC.seq to have a value in your subquery), but in your merge criteria, you're determining a match must have "LKC.seq" = 2. So I'm guessing that you simply never get a match to do an update with.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply