Update with default values in case clause

I have a single table like

CREATE TABLE Mytable (ID int identity, Name nvarchar(10)); 
GO 
INSERT INTO MyTable (Name) VALUES ('test1'); 
INSERT INTO MyTable (Name) VALUES ('test2'); 
GO 
ALTER TABLE MyTable ADD CONSTRAINT DF_Name DEFAULT('test') FOR Name;

Now I want to update my Name column like this:

Update MyTable
set name = case ID when 1 then DEFAULT END;

But I am getting this error:


Incorrect syntax near the keyword 'DEFAULT'

How can I use UPDATE with DEFAULT statement in the CASE clause?

You can't.

The relevant bit of the grammar for update is

SET column_name = { expression | default | null }

You can only use the default keyword in place of an expression, not inside an expression. So you would either need to just use the value of the default directly (could be looked up from the system views to be dynamic) or spilt it into two update statements with appropriate mutually exclusive where clauses - one using default and the second using an expression - if you really want to use that keyword.

StackExchange Database Administrators Q#135783, answer score: 8