SQL Update Based on Subquery-CodePudding

I am having trouble coming up with this SQL statement to go and update certain records that already exist. For the sake of brevity, I am using hard coded values.

What I want to do is the following: When there is already a record within the PersonXNotifyUser table where the UserID and NotifyUserID match, I want to make sure that I update the IsDeleted column to a value of 0, and also update the ModifiedBy and ModifiedDate columns accordingly. Here is what I have so far, which doesn't execute, but am hoping someone can help me out:

UPDATE: Since one of the answers assumed there is need for a subquery, I have added the Table Type and the stored procedure definition which expects the Table Valued Parameter

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

CodePudding user response：

Based on the inputs given in the question, it does not seems like you need a join or subquery for achieving this. Use a Simple Update

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
            )