In MySQL how to replace all NULL values in a particular field of a particular table?

To replace all NULL values in a particular field of a particular table, use UPDATE command with IS NULL property. The syntax is as follows:

UPDATE yourTableName SET yourColumnName=”yourValue’ WHERE yourColumnName IS NULL;

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table Employee_Information_Table
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(20),
   -> Salary int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.59 sec)

Insert some records in the table using insert command. The query to insert record is as follows:

mysql> insert into Employee_Information_Table(Name,Salary) values('John',NULL);
Query OK, 1 row affected (0.13 sec)

mysql> insert into Employee_Information_Table(Name,Salary) values('Carol',NULL);
Query OK, 1 row affected (0.17 sec)

mysql> insert into Employee_Information_Table(Name,Salary) values('Bob',NULL);
Query OK, 1 row affected (0.10 sec)

mysql> insert into Employee_Information_Table(Name,Salary) values('David',NULL);
Query OK, 1 row affected (0.17 sec)

mysql> insert into Employee_Information_Table(Name,Salary) values('Robert',NULL);
Query OK, 1 row affected (0.12 sec)

mysql> insert into Employee_Information_Table(Name,Salary) values('Mike',NULL);
Query OK, 1 row affected (0.24 sec)

mysql> insert into Employee_Information_Table(Name,Salary) values('Sam',NULL);
Query OK, 1 row affected (0.17 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from Employee_Information_Table;

The following is the output:

+----+--------+--------+
| Id | Name   | Salary |
+----+--------+--------+
|  1 | John   |   NULL |
|  2 | Carol  |   NULL |
|  3 | Bob    |   NULL |
|  4 | David  |   NULL |
|  5 | Robert |   NULL |
|  6 | Mike   |   NULL |
|  7 | Sam    |   NULL |
+----+--------+--------+
7 rows in set (0.00 sec)

Here is the query to replace all NULL values to a particular field of a particular table. The query is as follows:

mysql> update Employee_Information_Table
   -> set Salary=45500 where Salary IS NULL;
Query OK, 7 rows affected (0.23 sec)
Rows matched: 7 Changed: 7 Warnings: 0

Now check the table records once again. All NULL values have been updated with some value. The following is the query to list all records from the table using select statement:

mysql> select *from Employee_Information_Table;

The following is the output:

+----+--------+--------+
| Id | Name   | Salary |
+----+--------+--------+
|  1 | John   |  45500 |
|  2 | Carol  |  45500 |
|  3 | Bob    |  45500 |
|  4 | David  |  45500 |
|  5 | Robert |  45500 |
|  6 | Mike   |  45500 |
|  7 | Sam    |  45500 |
+----+--------+--------+
7 rows in set (0.00 sec)

George John

Updated on 30-Jul-2019 22:30:24

• Related Questions & Answers
• How to select particular range of values in a MySQL table?
• How to replace a particular character in a MySQL column?
• Bulk change all entries for a particular field in MySQL?
• How to update a field with a particular value if it is null in MySQL?
• How to calculate average of a particular field in MongoDB?
• MySQL replace values in a table?
• How can we fetch all the records from a particular MySQL table?
• Set all the columns of a MySQL table to a particular value with a single query
• How we can find all the triggers associated with a particular MySQL table?
• How to check table status of the tables in a particular MySQL database?
• How to find a particular varchar id in MySQL from a list of values?
• How to store a NULL value in a particular column of a DB2 table using COBOL-DB2 program?
• How to get the values of a particular row in a table in Selenium with python?
• Get the number of rows in a particular table with MySQL
• How to find and replace string in MySQL database for a particular string only?