你不能在MERGE
的THEN
部分使用任意的语句,the syntax对你被允许做的事情有很具体的说明.
如果您想在这样的行上使用THROW
,那么只需使用IF EXISTS... WHERE NOT EXISTS
即可.然后将MERGE
转换为正常连接UPDATE
.
因为它现在是两个语句,所以您将需要一个显式的事务和锁定提示.
SET NOCOUNT, XACT_ABORT ON;
BEGIN TRAN;
IF EXISTS (SELECT 1
FROM dbo.Source as src WITH (SERIALIZABLE)
WHERE NOT EXISTS (SELECT 1
FROM dbo.Target as tgt WITH (SERIALIZABLE, UPDLOCK)
WHERE tgt.ID = src.ID
)
)
THROW 50001, N'error: not all ids match!', 1;
UPDATE tgt
SET Name = src.Name
FROM dbo.Target as tgt
JOIN dbo.Source as src ON tgt.ID = src.ID;
COMMIT;
如果您希望错误消息告诉您第一个失败的ID,您可以将其存储在一个变量中,然后判断该变量是否为NULL
.
DECLARE @message nvarchar(1000);
SELECT TOP (1)
@message = CONCAT(N'error: id ', src.ID, N' is not in target')
FROM dbo.Source as src WITH (SERIALIZABLE)
WHERE NOT EXISTS (SELECT 1
FROM dbo.Target as tgt WITH (SERIALIZABLE, UPDLOCK)
WHERE tgt.ID = src.ID
);
IF @message IS NOT NULL
THROW 50001, @message, 1;