SQL Server MERGE statement with multiple UPDATES-CodePudding

I have a legacy stored procedure in SQL Server that I am trying to update. It is a big procedure, but to illustrate my concern, I will make it simpler.

The procedure is to update a user in User table. If it is a new user, they INSERT, otherwise UPDATE the data. Currently, they use a MERGE statement to accomplish this. I have to modify the procedure to handle PreviousAddress logic. The procedure needs to check if the user is an existing user, and the address passed in to the procedure is a new address, then we need to put the old address in PreviousAddress column and the new address goes in Address column.

Here is the current MERGE statement:

MERGE dbo.User AS TARGET
USING
(
    SELECT @USERID as USERID,
    @USERNAME as USERNAME,
    @ADDRESS as ADDRESS,
    @PREVIOUSADDRESS as PREVIOUSADDRESS
) as SOURCE
(
    USERID,
    USERNAME,
    ADDRESS,
    PREVIOUSADDRESS
) ON (TARGET.USERID = SOURCE.USERID)
WHEN NOT MATCHED THEN
    INSERT ( USERID,
       USERNAME,
       ADDRESS,
       PREVIOUSADDRESS )
    VALUES ( SOURCE.USERID,
       SOURCE.USERNAME,
       SOURCE.ADDRESS,
       SOURCE.PREVIOUSADDRESS )
WHEN MATCHED THEN
    UPDATE
       SET USERNAME = SOURCE.USERNAME,
           ADDRESS = SOURCE.ADDRESS,
           PREVIOUSADDRESS = SOURCE.PREVIOUSADDRESS

I want to do something like this, of course two MERGE statement are not possible but want to know how to achieve similar solution:

MERGE dbo.User AS TARGET
    USING
    (
        SELECT @USERID as USERID,
        @USERNAME as USERNAME,
        @ADDRESS as ADDRESS,
        @PREVIOUSADDRESS as PREVIOUSADDRESS
    ) as SOURCE
    (
        USERID,
        USERNAME,
        ADDRESS,
        PREVIOUSADDRESS
    ) ON (TARGET.USERID = SOURCE.USERID)
    WHEN NOT MATCHED THEN
        INSERT ( USERID,
           USERNAME,
           ADDRESS,
           PREVIOUSADDRESS )
        VALUES ( SOURCE.USERID,
           SOURCE.USERNAME,
           SOURCE.ADDRESS,
           SOURCE.PREVIOUSADDRESS )
    WHEN MATCHED AND TARGET.ADDRESS <> SOURCE.ADDRESS THEN
        UPDATE
           SET USERNAME = SOURCE.USERNAME,
               ADDRESS = SOURCE.ADDRESS,
               PREVIOUSADDRESS = TARGET.ADDRESS
    WHEN MATCHED AND TARGET.ADDRESS = SOURCE.ADDRESS THEN
        UPDATE
           SET USERNAME = SOURCE.USERNAME,
               ADDRESS = SOURCE.ADDRESS,
               PREVIOUSADDRESS = SOURCE.PREVIOUSADDRESS

My current solution is: at the beginning of the procedure, I go and get the current address from the USER table for the an existing user and if the address doesn't match, then store the existing address in memory. Then I use this stored address in the end to compare and update (after the MERGE statement is executed).

Something like this:

IF @USERID IS NULL AND @ADDRESS IS NOT NULL 
BEGIN
  SELECT @CURRENTADDRESS = ADDRESS FROM USER WHERE USERID = @USERID AND ADDRESS <> @ADDRESS
END

<<ORIGINAL MERGE STATEMENT>>

IF @CURRENTADDRESS IS NOT NULL
BEGIN
  UPDATE USER SET PREVIOUSADDRESS = @CURRENTADDRESS WHERE USERID = @USERID
END

Any ideas how to solve this problem with just the MERGE statement?

CodePudding user response：

You can use a CASE WHEN clause

MERGE dbo.User AS TARGET
    USING
    (
        SELECT @USERID as USERID,
        @USERNAME as USERNAME,
        @ADDRESS as ADDRESS,
        @PREVIOUSADDRESS as PREVIOUSADDRESS
    ) as SOURCE
    (
        USERID,
        USERNAME,
        ADDRESS,
        PREVIOUSADDRESS
    ) ON (TARGET.USERID = SOURCE.USERID)
    WHEN NOT MATCHED THEN
        INSERT ( USERID,
           USERNAME,
           ADDRESS,
           PREVIOUSADDRESS )
        VALUES ( SOURCE.USERID,
           SOURCE.USERNAME,
           SOURCE.ADDRESS,
           SOURCE.PREVIOUSADDRESS )
    WHEN MATCHED 
        UPDATE
           SET USERNAME = SOURCE.USERNAME,
               ADDRESS = SOURCE.ADDRESS,
               PREVIOUSADDRESS = (CASE WHEN  TARGET.ADDRESS <> SOURCE.ADDRESS THEN TARGET.ADDRESS
               ELSE SOURCE.PREVIOUSADDRESS END)