How do I delete blank rows in MySQL?

MySQLMySQLi Database

Use the delete command to delete blank rows in MySQL.

The syntax is as follows

delete from yourTableName where yourColumnName=' ' OR yourColumnName IS NULL;

The above syntax will delete blank rows as well as NULL row.

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

mysql> create table deleteRowDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> StudentName varchar(20)
   -> );
Query OK, 0 rows affected (0.57 sec)

Insert some records in the table using insert command.

The query is as follows

mysql> insert into deleteRowDemo(StudentName) values('John');
Query OK, 1 row affected (0.13 sec)
mysql> insert into deleteRowDemo(StudentName) values('');
Query OK, 1 row affected (0.18 sec)
mysql> insert into deleteRowDemo(StudentName) values('');
Query OK, 1 row affected (0.14 sec)
mysql> insert into deleteRowDemo(StudentName) values(NULL);
Query OK, 1 row affected (0.13 sec)
mysql> insert into deleteRowDemo(StudentName) values('Carol');
Query OK, 1 row affected (0.13 sec)
mysql> insert into deleteRowDemo(StudentName) values('Bob');
Query OK, 1 row affected (0.14 sec)
mysql> insert into deleteRowDemo(StudentName) values('');
Query OK, 1 row affected (0.18 sec)
mysql> insert into deleteRowDemo(StudentName) values('David');
Query OK, 1 row affected (0.35 sec)

Display all records from the table using select statement.

The query is as follows

mysql> select *from deleteRowDemo;

The following is the output

+----+-------------+
| Id | StudentName |
+----+-------------+
| 1  | John        |
| 2  |             |
| 3  |             |
| 4  | NULL        |
| 5  | Carol       |
| 6  | Bob         |
| 7  |             |
| 8  | David       |
+----+-------------+
8 rows in set (0.00 sec)

Here is the query to delete blank rows as well as NULL

mysql> delete from deleteRowDemo where StudentName='' OR StudentName IS NULL;
Query OK, 4 rows affected (0.18 sec)

Now let us check the table records once again.

The query is as follows

mysql> select *from deleteRowDemo;

The following is the output

+----+-------------+
| Id | StudentName |
+----+-------------+
| 1  | John        |
| 5  | Carol       |
| 6  | Bob         |
| 8  | David       |
+----+-------------+
4 rows in set (0.00 sec)
raja
Published on 29-Mar-2019 11:17:43
Advertisements