SQL Puzzle | How to alter a column from null to not null

In this puzzle you have to make changes to the table properties to make a column to NOT NULL from NULL. The catch here is that how you can handle the existing NULL values.

Script – DDL and INSERT Sample Data

--

CREATE TABLE Nulls
(
	 Id INT NOT NULL
	,Vals INT NULL
)
GO

INSERT INTO Nulls VALUES (1,NULL) ,(2,20),(3,30) , (4,NULL)
GO

SELECT * FROM Nulls
GO

--

If you directly use the alter command (Alter Table….Alter Column) you will get below error.

--

ALTER TABLE Nulls 
	ALTER COLUMN Vals INT NOT NULL

--

Msg 515, Level 16, State 2, Line 20
Cannot insert the value NULL into column ‘Vals’, table ‘master.dbo.Nulls’; column does not allow nulls. UPDATE fails.
The statement has been terminated.

Here first we have to update the NULL values to some other value. And then we can update the column definition.

–SOLUTION 1–

--

UPDATE x
SET x.Vals = -1 
FROM Nulls x 
WHERE x.Vals IS NULL
GO

ALTER TABLE Nulls 
	ALTER COLUMN Vals INT NOT NULL
GO

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com