Set a Column Value to NULL in SQL

Setting a Column Value to Null

To set a column value to NULL, use the SQL UPDATE statement, which allows modification of existing records in a table. The basic syntax for setting a column value to NULL is ?

Syntax

UPDATE table_name
SET column_name = NULL
WHERE conditions;

Where ?

• table_name: Replace this with the name of the table you intend to update.
• column_name: Substitute this with the name of the column to be updated.
• NULL: Denotes the NULL value in SQL.
• WHERE: This optional clause specifies the rows to be updated.

Example

First, let's create a table using the CREATE TABLE command as follows ?

CREATE TABLE Customers (
   Customer_ID integer, 
   Customer_Name varchar(20), 
   Gender varchar(2)
);

Following queries insert values into the above-created table ?

INTO Customers VALUES (1, 'Nidhi', 'F');
INSERT INTO Customers VALUES (2, 'Ram', 'M');
INSERT INTO Customers VALUES (3, 'Nikhil', 'M');
INSERT INTO Customers VALUES (4, 'Riya', 'F');

If you try to retrieve the contents of the table will be displayed as ?

Customer_ID	Customer_Name	Gender
1	Nidhi	F
2	Ram	M
3	Nikhil	M
4	Riya	F

The following example sets the Gender of the female customers to "NULL" ?

UPDATE 
Customers 
SET Gender = NULL 
WHERE Gender = 'F';
SELECT * FROM Customers;

The result of the above query would be as shown below

Customer_ID	Customer_Name	Gender
1	Nidhi
2	Ram	M
3	Nikhil	M
4	Riya

Setting Column Value to NULL

You can also set a column value to NULL without using the WHERE clause ?

Example

UPDATE Customers SET Gender = NULL;
SELECT * FROM Customers;

Output

Customer_ID	Customer_Name	Gender
1	Nidhi
2	Ram
3	Nikhil
4	Riya

If a constraint has been set to prevent a specific column value from being NULL, attempting to set it to NULL afterward will result in an error.

Example

Let's recreate the previous table by adding "NOT NULL" constraint to the column named "Gender".

CREATE TABLE Customers (
   Customer_ID integer, 
   Customer_Name varchar(20), 
   Gender varchar(2) NOT NULL
);

Let's populate the table ?

INSERT INTO Customers VALUES (1, 'Nidhi', 'F');
INSERT INTO Customers VALUES (2, 'Ram', 'M');
INSERT INTO Customers VALUES (3, 'Nikhil', 'M');
INSERT INTO Customers VALUES (4, 'Riya', 'F');

The created table would be as shown below

Customer_ID	Customer_Name	Gender
1	Nidhi	F
2	Ram	M
3	Nikhil	M
4	Riya	F

The following query tries to update the values of the "Gender" column to NULL ?

UPDATE Customers SET Gender = NULL WHERE Gender = 'F';

Output

ERROR: Gender cannot be NULL.

In this article, "How to Set a Column Value to Null in SQL," we've covered some fundamental points ?

• Include the table name in the UPDATE statement.
• Assign NULL to the specified column.
• Use a WHERE clause to select which rows to update.
• Execute the SQL query to apply the changes.