How to modify "null" to "not null" in sql

Question

I have a table where I need to change from "null" to "not null".

I have a table with following structure:

Created_By   Created_Date_Time   Modified_By    Modified_Date_Time
NULL            NULL                -1                 NULL
NULL            NULL                -1                 NULL
NULL            NULL                -1                 NULL

I need to change Modified_By from -1 to null.

I am trying with the following query but it gives me an error:

update BOM_Rules
set Modified_By = not null

Error:

Cannot insert the value NULL into column 'Modified_By', table 'dbo.BOM_Rules'; column does not allow nulls. UPDATE fails.

I am sure I am missing something important. Maybe alter table?

ALTER TABLE BOM_Rules
ALTER COLUMN Modified_By NVARCHAR(50) NOT NULL

I am using SQL2008R2.

There is no NOT NULL value. That term is only used in DDL and WHERE clauses. You have to actually set a value if you want something to be not null — Phil
– Phil, Commented Dec 19, 2012 at 22:28
set Modified_By = not null ought to give you the error Incorrect syntax near the keyword 'not'. as that is not valid syntax. Not an error about inserting NULL` — Martin Smith
– Martin Smith, Commented Dec 19, 2012 at 22:28
Your question is inconsistent. You say that you need to change 'from "null" to "not null"', but then you say that the Modified_By field contains -1, and that you want to change it to null. So, which is it? — voithos
– voithos, Commented Dec 19, 2012 at 22:33

BStateham · Accepted Answer · 2012-12-19 22:35:58Z

6

It sounds like the Modified_By column does not allow nulls. Try altering the column to allow nulls:

ALTER TABLE BOM_Rules
ALTER COLUMN Modified_By NVARCHAR(50) NULL

answered Dec 19, 2012 at 22:35

BStateham

1,62910 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martin Smith Over a year ago

This interpretation of the question makes the most sense to me. I presume they must have been running update BOM_Rules set Modified_By = null rather than the query they say in the question.

Collectives™ on Stack Overflow

How to modify "null" to "not null" in sql

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related