基于子查询的SQL更新

Question

我遇到了麻烦，这个SQL语句去更新某些已经存在的记录。为简洁起见，我使用硬编码值。

我想做的是:当PersonXNotifyUser表中已经存在UserID和NotifyUserID匹配的记录时，我要确保将IsDeleted列更新为0的值，并相应地更新ModifiedBy和ModifiedDate列。以下是我到目前为止所做的，它没有执行，但我希望有人能帮助我:

由于其中一个答案假设需要子查询，因此我添加了表类型和存储过程定义，该定义期望表值参数

CREATE TYPE dbo.GuidIDList
AS TABLE
(
ID [UNIQUEIDENTIFIER]
);
CREATE PROCEDURE [dbo].[PersonXNotifyUser_InsertUpdate]
(
,@UserID [UNIQUEIDENTIFIER]
,@NotifyUserIDs AS dbo.GuidIDList READONLY
,@EditingUserID [UNIQUEIDENTIFIER]
)
AS
SET NOCOUNT ON
UPDATE PersonXNotifyUser
SET IsDeleted = 0, ModifiedBy = @EditingUserID, ModifyDate = GETUTCDATE()
FROM (
SELECT
test.NotifyUserID
FROM (
SELECT ID FROM @NotifyUserIDs
)
AS test (NotifyUserID)
WHERE EXISTS
(SELECT PersonXNotifyUserID
FROM PersonXNotifyUser pnu
WHERE pnu.UserID = @UserID AND pnu.NotifyUserID = test.NotifyUserID
)
)

Answer 1

根据问题中给出的输入，似乎不需要连接或子查询来实现这一点。使用一个简单的更新

DECLARE @UserID UNIQUEIDENTIFIER = '45D9F7E4-E111-4E62-8B1A-118F7C7FB6A1'
DECLARE @EditingUserID UNIQUEIDENTIFIER = 'CDFDBD9A-87FB-4F68-B695-F4A39424C207'
UPDATE PersonXNotifyUser
SET
IsDeleted = 0, 
ModifiedBy = @EditingUserID, 
ModifyDate = GETUTCDATE()
WHERE UserID = @UserID 
AND EXISTS
(
SELECT
1
FROM @NotifyUserIDs
WHERE ID = PersonXNotifyUser.NotifyUserID
)