INSERT Record if NOT EXISTS in MySQL

Methods to insert records if not exist in MySQL

In this article, we discuss how to insert single or multiple records in SQL tables if they are not present. If records are already present then no updation occurs.

Before going to the actual content first let create a table and insert some dummy data into it.

CREATE TABLE student_details(
    student_id INT auto_increment,
   student_name VARCHAR(255),
   student_address VARCHAR(255),
    primary key (student_id));
INSERT INTO student_details (student_name,student_address) 
 VALUES("Mayank","Kolkata"),
("Raj","Delhi"),
("Abhay","Delhi"),
("Aman","Jaipur"),    
("Rahul","Mumbai"),
("Shubh","Merrut");
select * from student_details;

Output

student_id	student_name	student_address
1	Mayank	Kolkata
2	Raj	Delhi
3	Abhay	Delhi
4	Aman	Jaipur
5	Rahul	Mumbai
6	Shubh	Merrut

So this is our student_details table.

Now we will see how to insert single or multiple records if not exist in the table. Let first see the syntax or sample SQL query to do this task after that we will implement the query on our table.

INSERT NOT EXISTS Syntax

If a subquery returns any rows at all, NOT EXISTS subquery is FALSE. It means that if the subquery in the NOT EXIST clause is TRUE, it will return no rows.

Syntax:INSERT INTO table_name (column1, column2, ....)                                           SELECT * FROM (SELECT value1, value2,....) AS temp                                          WHERE NOT EXISTS (<conditional_subquery>);

table_name: It is the name of that table where we want to insert the data.

<conditional_subquery>: It is the sub-query including a select statement to get the row with a particular condition.

INSERT single record if NOT EXISTS in MySQL

Now with the help of the above query, we will see how to insert a single record if it is NOT EXISTS in MySQL.

INSERT INTO student_details (student_name,student_address) 
SELECT * FROM (SELECT 'Manish' AS student_name, 'Agra' AS student_address) AS temp 
WHERE NOT EXISTS ( SELECT student_name FROM student_details WHERE student_name = 'Manish' ) LIMIT 1

Output

student_id	student_name	student_address
1	Mayank	Kolkata
2	Raj	Delhi
3	Abhay	Delhi
4	Aman	Jaipur
5	Rahul	Mumbai
6	Shubh	Merrut
7	Manish	Agra

Here we see that one record with student_name=’Manish’ and student_address=’Agra’ is inserted.

Suppose we will fire this query again. Let see what happens.

 0 rows inserted. (Query took 0.0012 seconds.)

When we try to execute this query again we see that this time no record is inserted because the record is already present in the SQL table. So we can say that this query only inserts records when NOT EXIST in MySQL.

INSERT multiple records if NOT EXISTS in MySQL

What if we have more than one record to be inserted, and before every insert, we want to ensure that the record with the same column value does not exist. Let see how to write an SQL query for this.

INSERT INTO student_details (student_name, student_address) 
SELECT student_name, student_address FROM 
( SELECT student_name , student_address FROM 
( SELECT 'Ravi' as student_name , 'Shimla' as student_address ) AS temp_1 
WHERE NOT EXISTS ( SELECT student_name FROM student_details WHERE student_name = 'Ravi' ) 
UNION ALL 
SELECT student_name, student_address FROM ( SELECT 'Raju' as student_name , 'Bandra' as student_address ) AS temp_2 WHERE NOT EXISTS ( SELECT student_name FROM student_details WHERE student_name = 'Raju' ) ) 
alias_student_details

Output

student_id	student_name	student_address
1	Mayank	Kolkata
2	Raj	Delhi
3	Abhay	Delhi
4	Aman	Jaipur
5	Rahul	Mumbai
6	Shubh	Merrut
7	Manish	Agra
8	Ravi	Shimla
9	Raju	Bandra

So here we see another 2 records inserted in the SQL table. So we see how to insert multiple or single records if NOT EXIST in MySQL.