How to Set a Column Value to Null in SQL?

You can set a column value to NULL using the SQL UPDATE statement. Through the UPDATE statement, existing records in a table can be changed. The fundamental syntax to set a column value to NULL is as follows.

Syntax:

UPDATE table_name

set column_name=NULL

WHERE Conditions;

table_name: The name of the table you want to update should be replaced here.

NULL: It represents the NULL value in SQL.

WHERE: This statement has an optional part that specifies which rows should be updated.

column_name: The name of the column you want to update should be replaced here.

Updating a Column to NULL Value

Firstly, let’s create a table using the CREATE TABLE command:

-- create a table
CREATE TABLE students (Sr_No integer,Name varchar(20), Gender varchar(2));
-- insert some values
INSERT INTO students VALUES (1, 'Nikita', 'F');
INSERT INTO students VALUES (2, 'Akshit', 'M');
INSERT INTO students VALUES (3, 'Ritesh', 'F');
INSERT INTO students VALUES (4, 'Himani', 'F');
-- fetch some values
SELECT * FROM students ;

The table would look like this:

student Table

Case 1: To UPDATE Column value, use the below command

Syntax:

UPDATE TABLE [TABLE_NAME]

Case 2: To set column value to NULL, use the below command

Syntax:

UPDATE [TABLE_NAME] set [COLUMN_NAME] = NULL where [CRITERIA]

Query

UPDATE students set Gender = NULL where Gender='F';
SELECT * FROM students;

Output

output

Column value can also be set to NULL without specifying the ‘where’ condition.

Query

UPDATE students set Gender = NULL;
SELECT * FROM students;

Output

output

If you have set a constraint that a particular column value can not be NULL, and later try to set it as NULL, then it will generate an error.

Example:

-- create a table
CREATE TABLE students (Sr_No integer,Name varchar(20), Gender varchar(2) NOT NULL);
-- insert some values
INSERT INTO students VALUES (1, 'Nikita', 'F');
INSERT INTO students VALUES (2, 'Akshit', 'M');
INSERT INTO students VALUES (3, 'Ritesh', 'F');
INSERT INTO students VALUES (4, 'Himani', 'F');
-- fetch some values
SELECT * FROM students;

Output

students table

Query

UPDATE students set Gender = NULL where Gender ='F';

Output

ERROR: Gender may not be NULL.

Conclusion

In this article ‘How to Set a Column Value to Null in SQL’, we have reached to some of the basic conclusions, that are listed below.

• Use the table name in the UPDATE statement.
• Put NULL in the column name.
• To select which rows to update, use a WHERE clause.
• To make the modification, run the SQL query.