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)