T-sql 2012 update statement in merge is not working

Transact-SQL
1

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?

2

There;s a difference in the Merge and the Update:

Merge: and LKC.seq = 1
Udpate: WHERE LKC.seq = 2