SQL Server 2014 – Update with Default Values in CASE Clause

sql serversql server 2014

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?

Best Answer

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.

Related Solutions

SQL Server – Update All Rows with Random Foreign Key from Another Table

Not going to go through what you're trying to accomplish, but for your update, you're using a TOP without an ORDER BY. While SQL Server doesn't guarantee ordering for such queries, it doesn't just randomly choose, either. If you want to force a random row to be returned, you'll need to order by a row-level random number. For that, you can use ORDER BY ABS( CHECKSUM( NEWID() ) ).

Try it out against sys.objects to see the behavior of explicitly ordering randomly.

SELECT  TOP 1 name
FROM    sys.objects
ORDER BY ABS( CHECKSUM( NEWID() ) );

And good luck with whatever it is you're doing.

Edit:

@jean points out in the comments just NEWID() is all that is necessary, which is 100% correct, I just muscle-memory the ABS( CHECKSUM() ) since I tend to need row-level randomization with a % operator.

Edit 2:

So something like this, then?

UPDATE  t
    SET CategoryID = nt.CategoryID
FROM    dbo.Topic t
INNER JOIN (    SELECT  t.ID, ucl.CategoryID, 
                        Ordinal = ROW_NUMBER() OVER (
                            PARTITION BY t.ID
                            ORDER BY NEWID() )
                FROM    dbo.Topic t
                INNER JOIN dbo.UserCategoryLink ucl
                    ON  t.UserID = ucl.UserID ) nt
    ON  t.ID = nt.ID
WHERE   nt.Ordinal = 1;

SQL Server – Resolving Snapshot Isolation Transaction Aborted Due to Update Conflict

When deleting from the parent table, SQL Server must check for the existence of any FK child rows that refer to that row. When there is no suitable child index, this check performs a full scan of the child table:

If the scan encounters a row that has been modified since the delete command's snapshot transaction started, it will fail with an update conflict (by definition). A full scan will obviously touch every row in the table.

With a suitable index, SQL Server can locate and test just the rows in the child table that could match the to-be-deleted parent. When these particular rows have not been modified, no update conflict occurs:

Note that foreign key checks under row versioning isolation levels take shared locks (for correctness) as well as detecting update conflicts. For example, the internal hints on the child table accesses above are:

PhyOp_Range TBL: [dbo].[Child]
    Hints( READ-COMMITTEDLOCK FORCEDINDEX DETECT-SNAPSHOT-CONFLICT )

Sadly this is not currently exposed in execution plans.

Best Answer

Related Solutions

SQL Server – Update All Rows with Random Foreign Key from Another Table

SQL Server – Resolving Snapshot Isolation Transaction Aborted Due to Update Conflict

Related Question