How to Set Null in MySQL

In this tutorial, we aim at exploring how to set NULL values in MySQL.

It is essential to update specific table fields in MySQL as NULL if there is no value. This NULL value addition helps in data storage, accessibility, and analysis.

One might need to set a particular field of a validation form as NULL if the user gives no input. MySQL helps tackle this with the help of the UPDATE TABLE statement.

Let us understand how this method works.

Before we begin, let’s create a dummy dataset to work with using a table, student_details, with a few rows in it.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

Setting Null Values in MySQL

The basic syntax of this technique can be illustrated as follows.

UPDATE name_of_the_table SET column_name = NULL WHERE <condition>;

Based on a particular condition, let us assign NULL values to the stu_lastName column of the student_details table.

UPDATE student_details SET stu_lastName = NULL WHERE stu_id IN (1,2,3);

The output of the code above block can be illustrated with the following query.

SELECT * from student_details;

Output:

stu_id	stu_firstName	stu_lastName
1	      Preet	        NULL
2	      Rich	        NULL
3	      Veron	        NULL
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

As shown in the code above block, the students with stu_id as 1, 2, or 3 have been assigned NULL values to their last names.

Therefore, with the help of the UPDATE statement, we can efficiently set null values for particular fields in a table in MySQL.