Moving data between fields in destination table in T-SQL MERGE

I have a SQL MERGE statement that does an UPDATE if a value exists (and an INSERT if it does). I'd like to record the last few "historical" values in the row when an UPDATE happens, sort of like this:

MERGE into desttable as dest
USING sourcetable as src
on src.ref = dest.ref

WHEN MATCHED THEN
UPDATE SET dest.oldvalue3=dest.oldvalue2, 
dest.oldvalue2=dest.oldvalue1, 
d开发者_高级运维est.oldvalue1=dest.value, 
dest.value=src.newvalue

WHEN NOT MATCHED THEN
INSERT ...

... so essentially, the existing "value" is shuffled down to "oldvalue1", "oldvalue1" becomes "oldvalue2" etc. However, when I run this, every column gets set to "value" - the UPDATE statement is obviously setting all the fields at once. Is there any way to achieve what I'm trying to do?

You can left outer join desttable in the using clause and use columns from desttable as source to the update.

MERGE INTO desttable AS dest
USING (SELECT s.ref,
              s.newvalue,
              d.oldvalue2,
              d.oldvalue1,
              d.value 
       FROM sourcetable AS s
         LEFT OUTER JOIN desttable AS d
           ON s.ref = d.ref) AS src
ON src.ref = dest.ref
WHEN MATCHED THEN
UPDATE SET dest.oldvalue3=src.oldvalue2, 
           dest.oldvalue2=src.oldvalue1, 
           dest.oldvalue1=src.value, 
           dest.value=src.newvalue
;

https://data.stackexchange.com/stackoverflow/q/114412/