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

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

语法请参考：

按照语法编写语句

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);