Execute INSERT if table is empty in MySQL?

You can execute insert if table is empty with the help of subquery. For that, work on not exists condition with subquery.

The below syntax will work only when your table is empty. If your table is not empty then it will not insert the record. The syntax is as follows:

INSERT INTO yourTableName(yourColumnName)
SELECT ‘anyValue’
WHERE NOT EXISTS (SELECT *FROM yourTableName);

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

mysql> create table ExecuteInsertDemo
   -> (
   -> Name varchar(20)
   -> );
Query OK, 0 rows affected (0.67 sec)

Let us insert record in the table using insert command. The query to insert record is as follows:

mysql> insert into ExecuteInsertDemo values('John');
Query OK, 1 row affected (0.19 sec)

Let’s say our table is not empty. It has only a single record. If you execute insert command then MySQL will not enter that record in the table.

The query to execute insert is as follows:

mysql> insert into ExecuteInsertDemo(Name)
   -> select 'Larry'
   -> where not exists (select *from ExecuteInsertDemo);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

Display the record using select statement. The query is as follows:

mysql> select *from ExecuteInsertDemo;

The following is the output:

+------+
| Name |
+------+
| John |
+------+
1 row in set (0.00 sec)

Yo need to delete the record from the table to run the query we saw above. Use truncate command. The query is as follows:

mysql> truncate table ExecuteInsertDemo;
Query OK, 0 rows affected (1.10 sec)

Run the above query to execute insert command. The query is as follows:

mysql> insert into ExecuteInsertDemo(Name)
   -> select 'Larry'
   -> where not exists (select *from ExecuteInsertDemo);
Query OK, 1 row affected (0.33 sec)
Records: 1 Duplicates: 0 Warnings: 0

Display the record from the table using select. The query is as follows:

mysql> select *from ExecuteInsertDemo;

The following is the output:

+-------+
| Name  |
+-------+
| Larry |
+-------+
1 row in set (0.00 sec)

Look at the sample output, ’Larry’ has been inserted successfully when the table was empty.

Ankith Reddy

e

Updated on: 30-Jul-2019

2K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started