SQL Server 2008中利用merge into关键实现insert/update自动匹配(类似于MySQL中的For Update关键字)

SQL Server 2008中利用merge into关键实现insert/update自动匹配(类似于MySQL中的For Update关键字)

语法请参考：

https://technet.microsoft.com/zh-cn/library/bb510625.aspx

按照语法编写语句

DECLARE @OpenID VARCHAR(150);
SET @OpenID = '5';


MERGE INTO wx AS T 
USING (SELECT [User_ID] FROM wx WHERE OpenId = @OpenId) AS S ON T.[User_ID]=S.[User_ID]
WHEN MATCHED THEN 
	UPDATE SET T.Invalid = 0, T.Updater = 1, T.UpdateTime = GETDATE() 
WHEN NOT MATCHED THEN 
	INSERT (OpenId, Creator, CreateTime, Invalid) VALUES(@OpenID, 1, GETDATE(), 0);

发现添加语句不起作用

仔细看了语句，再逐行分析，突然想起来NOT MATCHED后面其实是省略了一个关键字BY TARGET的

意思是：TARGET中没有，而SOURCE中有的话，向TARGET表中添加一条数据

                但其实在上例中，要添加数据时，openid是不存在的

调整语句，执行成功，问题解决：

DECLARE @OpenID VARCHAR(150);
SET @OpenID = '5';


MERGE INTO wx_User AS T 
USING (SELECT @OpenID) AS S(OpenId) ON T.[OpenId] = S.[OpenId] 
WHEN MATCHED THEN 
	UPDATE SET T.Invalid = 0, T.Updater = 1, T.UpdateTime = GETDATE() 
WHEN NOT MATCHED THEN 
	INSERT (OpenId, Creator, CreateTime, Invalid) VALUES(@OpenID, 1, GETDATE(), 0);