
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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)
- Related Articles
- How to select particular range of values in a MySQL table?
- How to update a field with a particular value if it is null in MySQL?
- How to replace a particular character in a MySQL column?
- Bulk change all entries for a particular field in MySQL?
- How can we fetch all the records from a particular MySQL table?
- How to calculate average of a particular field in MongoDB?
- MySQL replace values in a table?
- How to check table status of the tables in a particular MySQL database?
- How we can find all the triggers associated with a particular MySQL table?
- How to store a NULL value in a particular column of a DB2 table using COBOL-DB2 program?
- How to find a particular varchar id in MySQL from a list of values?
- How to get the values of a particular row in a table in Selenium with python?
- Set all the columns of a MySQL table to a particular value with a single query
- How to get values of all rows in a particular column in openpyxl in Python?
- How to find and replace string in MySQL database for a particular string only?
