Search code examples
sqlsql-serveralter-table

How do I set the DEFAULT value for a non-nullable column to the TOP 1 result of a SELECT query?

I'm trying to add a non-nullable column to an already existing table, and I read somewhere that I need a DEFAULT value in order to do that.
The value for the DEFAULT needs to be equal to the TOP 1 user_id result of the SELECT query shown below. I tried using a variable, but this didn't work.

This is what I tried:

DECLARE @latestuserid AS int
SELECT TOP 1 @latestuserid = user_id from Employees ORDER BY user_id DESC
ALTER TABLE Control ADD nextuserid INT NOT NULL DEFAULT (@latestuserid)

However I get the error:

Variables are not allowed in the ALTER TABLE statement.

If I can't use variables, how do I do this if I don't always know the exact value of that SELECT statement?

Solution

  • You can first define the desired column as nullable and then execute an update command for the new column. After that, you can make it not nullable.