Multiple case when in an update statement

Viewed 42

I have two legacy procedures to update user data, one uses a MERGE statement and the other use the simple UPDATE statement. In both I have to write a logic to update the address, city, state, and zip. If the address is new, update the old address in PreviousAddress fields and new address goes in Address fields.

To keep it simple, I have tone downed the problem.

create table t1 (id int, address varchar(25), city varchar(25), state varchar(25), zip varchar(25), paddress varchar(25), pcity varchar(25), pstate varchar(25), pzip varchar(25))

insert into t1 values (1, '1 roger st', 'edison', 'nj', '98211', null, null, null, null)

declare @address varchar(25) = '6 roger st'
declare @city varchar(25) = 'edison'
declare @state varchar(25) = 'nj'
declare @zip  varchar(25) = '98191'
declare @paddress varchar(25) = null
declare @pcity varchar(25) = null
declare @pstate varchar(25) = null
declare @pzip  varchar(25) = null

update t1 
  set address = @address, city=@city, state=@state, zip=@zip,
  paddress = COALESCE(CASE when Address <> @address THEN Address ELSE @pAddress END, PAddress),
  pcity = COALESCE(CASE when city <> @city THEN city ELSE @pcity END, pcity),
  pstate = COALESCE(CASE when [state] <> @state THEN state ELSE @pstate END, pstate),
  pzip = COALESCE(CASE when zip <> @zip THEN zip ELSE @pzip END, pzip)

select * from t1

I thought I have solved the problem, but the result is not what is desired, if the address fields are different, the solution works. But if few of the address fields didn't change, then the solution fails. I want to make sure if any address field change, I need to update all previous address fields with the new one. In the example above, ONLY the address and zip changed, not the city, and state. City and State is not updated in previous city and state with edison and nj but shows null.

How can we solve it for both UPDATE and MERGE statements?

Here is my merge statement:

MERGE dbo.User AS TARGET
    USING
    (
        SELECT @USERID as USERID,
        @USERNAME as USERNAME,
        @ADDRESS as ADDRESS,
        @City as CITY,
        @State as STATE,
        @ZIP as ZIP,
        @PREVIOUSADDRESS as PREVIOUSADDRESS,
        @PREVIOUSCity as PREVIOUSCITY,
        @PREVIOUSSTATE as PREVIOUSSTATE,
        @PREVIOUSZIP as PREVIOUSZIP
    ) as SOURCE
    (
        USERID,
        USERNAME,
        ADDRESS,
        CITY,
        STATE,
        ZIP,
        PREVIOUSADDRESS,
        PREVIOUSCITY,
        PREVIOUSSTATE,
        PREVIOUSZIP
    ) ON (TARGET.USERID = SOURCE.USERID)
    WHEN NOT MATCHED THEN
        INSERT ( USERID,
           USERNAME,
           ADDRESS,
           CITY,
           STATE,
           ZIP,
           PREVIOUSADDRESS,
           PREVIOUSCITY,
           PREVIOUSSTATE,
           PREVIOUSZIP )
        VALUES ( SOURCE.USERID,
           SOURCE.USERNAME,
           SOURCE.ADDRESS,
           SOURCE.CITY,
           SOURCE.STATE,
           SOURCE.ZIP,
           SOURCE.PREVIOUSADDRESS,
           SOURCE.PREVIOUSCITY,
           SOURCE.PREVIOUSSTATE,
           SOURCE.PREVIOUSZIP


)
        WHEN MATCHED 
            UPDATE
               SET USERNAME = SOURCE.USERNAME,
                   ADDRESS = SOURCE.ADDRESS,
                   ADDRESS = SOURCE.CITY,
                   ADDRESS = SOURCE.STATE,
                   ADDRESS = SOURCE.ZIP,
                   PREVIOUSADDRESS = (CASE WHEN  TARGET.ADDRESS <> SOURCE.ADDRESS THEN TARGET.ADDRESS
                   ELSE SOURCE.PREVIOUSADDRESS END),
                   PREVIOUSCITY = (CASE WHEN  TARGET.CITY <> SOURCE.CITY THEN TARGET.CITY
                   ELSE SOURCE.CITY END),
                   PREVIOUSSTATE = (CASE WHEN  TARGET.STATE <> SOURCE.STATE THEN TARGET.STATE
                   ELSE SOURCE.STATE END),
                   PREVIOUSZIP = (CASE WHEN  TARGET.ZIP <> SOURCE.ZIPTHEN TARGET.ZIP
                   ELSE SOURCE.PREVIOUSZIP END)
0 Answers
Related