Why this update statement is not getting compiled ? According to the msdn it is perfectly right.
UPDA开发者_如何学CTE dbo.UserProperties
SET Value = MergeFrom.Value
FROM dbo.UserProperties MergeFrom ,
dbo.UserProperties MergeTo
WHERE MergeFrom.Field = MergeTo.Field
AND MergeTo.[Key] = CAST(@MergeToUserID AS NVARCHAR(50))
AND MergeFrom.[Key] = CAST(@MergeFromUserID AS NVARCHAR(50))
I think you need to reference the Alias in the UPDATE for a self JOIN. At least I just tried an UPDATE with a self JOIN and the following kind of syntax worked for me.
UPDATE MergeTo
SET MergeTo.Value = MergeFrom.Value
FROM dbo.UserProperties AS MergeFrom INNER JOIN
dbo.UserProperties AS MergeTo ON MergeFrom.Field = MergeTo.Field
WHERE MergeTo.[Key] = CAST(@MergeToUserID AS NVARCHAR(50))
AND MergeFrom.[Key] = CAST(@MergeFromUserID AS NVARCHAR(50))
精彩评论