Update statement with case statement in SQL Server

It is very important to know that how case statement will behave when used in Update query.
As we know case statement can have multiple "when" and else is not required.
So if the query is executed where no condition is met defined in case statement then the row will be updated to null value. So to avoid this, you need else statement with whatever value you want to keep.

See example below:

'create table
CREATE TABLE #test (worktime DATETIME)

' insert current time
INSERT INTO #test( worktime ) VALUES  ( GETDATE());

' check value
SELECT * FROM #test

' now write case statement with no else and not meeting any condition

UPDATE #test

SET worktime = CASE WHEN worktime IS NULL THEN GETDATE() 

                                   WHEN worktime < '1/1/2010' THEN GETDATE()-7 

END

Check the value in table, the row will be updated to null.

So always mention else part of case statement when using case.

HTH,