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

淡淡的烟草味﹌ 2022-07-21 12:24 183阅读 0赞

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

语法请参考：

[https://technet.microsoft.com/zh-cn/library/bb510625.aspx][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);

[https_technet.microsoft.com_zh-cn_library_bb510625.aspx]: https://technet.microsoft.com/zh-cn/library/bb510625.aspx