Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
185 Views
