How to Set a Column Value to NULL in SQLite?

SQLite is a lightweight and self-contained relational database management system in short RDBMS. Its has a server-less architecture which makes it a better option for small desktop and mobile applications. It also requires very low configuration which eventually helps the developer to integrate it into any applications with great ease. In this article, we are going to explore the topic “how to set a column value to null“. We are going to explore all its required concepts with clear and concise examples.

How to Set a Column Value to NULL in SQLite

To set a column value to NULL, we have to use an UPDATE statement. The update statement is specifically used in modifications of existing records in a table. We will be using an update statement to set a single column or multiple columns to NULL. We can also use the WHERE clause along with the UPDATE statement to selectively set specific rows’ column(s) to NULL.

Syntax:

UPDATE table_name
SET column_name = NULL
WHERE (Specify condition);

Setting Up a Table in SQLite

In order to perform operations, we need to create a table in our database first. We can simply create a table with below query.

Table : geeksforgeeks

Query:

CREATE TABLE geeksforgeeks (
    id INTEGER PRIMARY KEY,
    name TEXT,
      rank INTEGER,
    total_score INTEGER
);

Now, we have created a table, lets add some data to it and display it. We can simply add the data to our table with below query.

Query:

--Data Insertion

INSERT INTO geeksforgeeks(id, name, rank, total_score)
VALUES(101,'Vishu',01,500); 
INSERT INTO geeksforgeeks(id, name, rank, total_score)
VALUES(102,'Aayush',02,400); 
INSERT INTO geeksforgeeks(id, name, rank, total_score)
VALUES(103,'Neeraj',03,450); 
INSERT INTO geeksforgeeks(id, name, rank, total_score)
VALUES(104,'Sumit',04,350); 
INSERT INTO geeksforgeeks(id, name, rank, total_score)
VALUES(105,'Vivek',05,200); 

--Displaying our table's data 

SELECT * From geeksforgeeks;

Output:

Table – geeksforgeeks

Now, we are are done with creating the table and inserting data to it. Lets move to some case based examples.

Example of How to Set a Column Value to NULL

Example 1: Setting a single column value to NULL

Case 1: Without WHERE clause

In this case, we are going to set a single column value to NULL. For this example, we are going to set all rank column values to NULL. Lets see the query.

Query:

UPDATE geeksforgeeks
SET rank = NULL;

SELECT * From geeksforgeeks;

Output:

Single Column Without Where Clause

Explanation: All the values of rank column is NULL now. Point to notice here is that, we have not used where clause here. In result all the values of rank column is NULL. If want only some specific rows to have a Null values in that particular column, we must specify conditions in WHERE clause.

Case 2: With WHERE clause

In this case, we will set only some specific row’s rank column to have NULL value. Lets see the query.

Query:

UPDATE geeksforgeeks
SET rank = NULL
WHERE rank < 4;

SELECT * From geeksforgeeks;

Output:

Single Column with Where Clause

Explanation: Here we can clearly see that some specific row’s rank column have NULL values. In query, we have specified that only rows with id column having value less than 4 should have NULL values in there rank column.

Example 2: Setting Multiple Columns Value to Null.

Case 1: Without WHERE clause

In this case, we are going to set multiple columns values to NULL. Unlike in previous example, we will be updating multiple columns values to NULL. Lets see the query.

Query:

UPDATE geeksforgeeks
SET rank = NULL, total_score = NULL;

SELECT * From geeksforgeeks;

Output:

Multiple Columns Without Where Clause

Explanation: Here we can clearly see that both the columns rank and total_score have NULL values for all the rows. Since we have not specified any conditions, therefore all the rows have NULL values in those two columns.

Case 2: With WHERE clause

In this case, we will be using WHERE clause along with UPDATE statement. We will be updating some specific row’s rank and total_score column value to NULL. Lets see the query.

Query:

UPDATE geeksforgeeks
SET rank = NULL, total_score = NULL
WHERE id IN (102,104,105);

SELECT * From geeksforgeeks;

Output:

Multiple Columns With Where Clause

Explanation: In the above image, we can clearly observe that unlike previous example, only some specific rows have NULL value in there rank and total_score column. We have specified in the query that rows where id’s column value matches with 102, 104, 105 should have NULL values in the said column.

Conclusion

SQLite is a light-weighted and has a server-less architecture. We can easily integrate it into any applications. In SQLite to set a column value to NULL , we have to use UPDATE statement for it. We can also use WHERE clause, if we want only some specific row’s column to have NULL value. We have covered its various example and real-life cases such as setting NULL values in a single column as well as in multiple columns. Now you have a good understanding on how to set a column value to NULL. Now you can easily perform queries related to it and can get the desired output.