INSERT record if NOT EXISTS in MySQL

This article will discuss how to insert a record IF NOT EXISTS in MySQL. We will be using MySQL subqueries with NOT EXISTS clause to fulfill the purpose.

Table of Contents

1. INSERT NOT EXISTS Syntax
2. INSERT single record if NOT EXISTS in MySQL
3. INSERT multiple record if NOT EXISTS in MySQL
4. INSERT record or UPDATE if they EXIST in MySQL

Let us get started by making the data that is to be used across. We will be creating a table customer_details and inserting data into it.

CREATE TABLE customer_details (
    customer_id INT auto_increment,
    customer_name VARCHAR(255),
    customer_address VARCHAR(255),
    primary key (customer_id));
INSERT INTO customer_details (customer_name,customer_address) 
 VALUES("Gustav","56A Denmark"),
("Henric","255 USA"),
("Richa","78 India"),
("Margit","58 Canada"),	
("Henric","18 Libya"),
("Henric","43 Sweden"),
("Richa","78 Singapore"),
("Henric","255 Italy"),
("Rohit","899 Bangladesh");

Output:-

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 your_table_name (column1, column2, ....)
SELECT * FROM (SELECT value1, value2,....) AS temp
WHERE NOT EXISTS (<conditional_subquery>);

• your_table_name – is the name of your table where you want to insert the data.
• column1, column2, …. – is the column list in your_table_name.
• <conditional_subquery> – is the sub-query including a select statement to get the row with a particular condition.

INSERT single record if NOT EXISTS in MySQL

In this section, we will be inserting a record to the table customer_details, but we will check if the customer_name already exists. If yes- then it will not insert the record; else, it will. Let us look into the below example query to have a better understanding.

INSERT INTO customer_details (customer_name,customer_address)
SELECT * FROM (SELECT 'Veronica' AS customer_name, '552 NewYork USA' AS customer_address) AS temp
WHERE NOT EXISTS (
    SELECT customer_name FROM customer_details WHERE customer_name = 'Veronica'
) LIMIT 1;

Action Message Output Response:-

1 row(s) affected Records: 1 Duplicates: 0 Warnings: 0

Here in the subquery with the NOT EXISTS clause, we are selecting the record from table customer_details. If the row does not exist in the table, then FALSE will be returned. Since there is a ‘NOT‘ keyword before EXISTS keyword, the query will INSERT the row.

SELECT * FROM customer_details;

Output:-

Figure 2 shows that the record has been inserted.

Since the record exists in the table with customer_name=’Veronica‘ , let us again try and insert the record with the same customer_name. Observe the below query and response message.

INSERT INTO customer_details (customer_name,customer_address)
SELECT * FROM (SELECT 'Veronica' AS customer_name, '552 NewYork USA' AS customer_address) AS temp
WHERE NOT EXISTS (
    SELECT customer_name FROM customer_details WHERE customer_name = 'Veronica'
) LIMIT 1;

Action Message Output Response:-

0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0

This time the record was not inserted as customer_name ‘Veronica’ already existed in the table customer_details.

INSERT multiple record 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 us take an example to add two rows for customers ‘Suveer’ and ‘Jenefir’ only if the records with names ‘Suveer’ and ‘Jenefir’ do not exist. Observe the below query for the solution.

INSERT INTO customer_details (customer_name, customer_address)
SELECT customer_name, customer_address
FROM
(
  SELECT customer_name , customer_address
  FROM
  (
     SELECT 'Suveer' as customer_name , '28 Street North America' as customer_address 
  ) AS temp_1
  WHERE NOT EXISTS
  (
     SELECT customer_name FROM customer_details WHERE customer_name = 'Suveer'
  )
  UNION ALL
  SELECT customer_name, customer_address
  FROM
  (
     SELECT 'Jenefir' as customer_name , '28 Canada' as customer_address 
  ) AS temp_2
  WHERE NOT EXISTS
  (
     SELECT customer_name FROM customer_details WHERE customer_name = 'Jenefir'
  )
) alias_customer_details;

Action Message Output Response:-

2 row(s) affected Records: 2 Duplicates: 0 Warnings: 0

The concept is the same as having a subquery within the NOT EXISTS clause with an additional UNION query to join both the INSERTS.

SELECT * FROM customer_details;

Output:-

Figure 3 shows that both the records have been inserted to table customer_details.

INSERT record or UPDATE if they EXIST in MySQL

There are other methods to insert records into the MySQL table or update if they are already present.

1. Using – on duplicate key update.
2. Using – IGNORE INTO
3. Using – REPLACE INTO

Go through the details of each from Insert into a MySQL table or update if it exists.

We hope this article helped you with insert records if not exists in MySQL. Good Luck !!!