Thursday, November 3, 2016

In Place Updates of Two Fields To Each Other in SQL

Not really sure how to classify this, but there are cases when you want to do an update of data in your system where you update one field to another field and set the other field to NULL.  Those of us that have had to approach this problem before know what the answer is, but I decided to create an example.

So the situation is this:  I have a table with two columns, and I want the first column to be overridden by the second column, and the second column to be set to NULL, if and ONLY if the second column is NOT null.  



The final output I want to be is this:

As you can see, Four was not overridden by Null, but One and Two in Column UserOne were updated by the values in the field UserTwo.

The reason I thought this was interesting as a problem was because of the question I was asked by a junior developer asking how he would track the update step to make sure that he updated the fields to NULL and I realized that I had never really thought about it because SQL Server handles this situation quite elegantly.  You don't HAVE to track what was updated, when doing the update it will use the values that are available at time of update, it does not do in order updates at all.

In reality the only statement you have to use is provided as a single update in the example below.  Order of the columns being set doesn't matter either.

IF object_id('tempdb..#tmp_testexample') IS NOT NULL
BEGIN
   DROP TABLE #tmp_testexample
END

create table #tmp_testexample
(
       ID int identity(1,1)
       ,UserOne varchar(255)
       ,UserTwo varchar(255)
)

insert into #tmp_testexample
(

       UserOne,UserTwo
)
SELECT
'One','Two'
UNION
Select 'Two','Three'

UNION SELECT 'Four',null

SELECT * FROM #tmp_testexample

select
*
from #tmp_testexample
where UserTwo is not null
       and UserOne <> UserTwo

update tta
set tta.UserOne = tta.UserTwo
, tta.UserTwo = NULL
from #tmp_testexample tta
where UserTwo is not null
       and UserOne <> UserTwo


select
*
from #tmp_testexample

This produces the output:

Original Data Set:

ID
UserOne
UserTwo
1
Four
NULL
2
One
Two
3
Two
Three
Example of what I want to update:

ID
UserOne
UserTwo
2
One
Two
3
Two
Three
Final Result after single update statement:

ID
UserOne
UserTwo
1
Four
NULL
2
Two
NULL
3
Three
NULL