Altering the Table to assign default value in case of NULL

Question

We have a table in Azure Sql Database. Currently one of the columns in the table is accepting null . I want to modify the column like whenever NULL occurs from the source data, it should be assigned to a default value. How can I change it

Thanks

Answer 1

Here is how you can specify a default value for a column (column_b in below example)

CREATE TABLE dbo.doc_exz (column_a INT, column_b INT); -- Allows nulls.
GO
INSERT INTO dbo.doc_exz (column_a) VALUES (7);
GO
ALTER TABLE dbo.doc_exz
  ADD CONSTRAINT DF_Doc_Exz_Column_B
  DEFAULT 50 FOR column_b;
GO

You can update rows with a null value stored already on the column:

UPDATE dbo.doc_exz
SET column_b = 50
WHERE column_b is null

Let me know if this helps in your scenario.

Answer 2

Hello Alberto,

Thanks for your quick solution.. It worked !!!