5

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?

1
  • 3
    You have an answer now so you know why this doesn't work. But if it worked, are you sure you wanted to update all the rows of the table? And put NULL in all the rows (except for the one with id=1 which would get the default value)? Did you really want that? Apr 19 '16 at 17:09
8

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.

1
  • If this was in a script or stored procedure, you could also save that default value into a variable and use that in the CASE statement. Apr 19 '16 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.