How to Insert Row If Not Exists in SQL

SQL’s “Insert If Not Exists” feature acts like a security guard for your database, preventing duplicate entries that can cause errors and disrupt data analysis. This operation is crucial for data integrity and is commonly used in relational databases. SQL offers multiple ways to perform this operation, and we’ll cover the “INSERT IF NOT EXISTS” operation, which inserts records only if they don’t already exist.

Creating the Foundation: Database Setup

Step 1: Create the Database

The provided SQL commands create a new database named “gfg_practice” using the “CREATE DATABASE gfg_practice;” statement. The “USE gfg_practice;” statement then selects the newly created database, making it the active database for subsequent SQL queries. This allows you to perform operations, create tables, and manipulate data within the “gfg_practice” database.

Query:

CREATE DATABASE gfg_practice;
USE gfg_practice;

Step 2: Create the table

The provided SQL statement creates a table named “Student” with two columns: “id” and “name“. The “id” column is defined as an integer and is set as the primary key for the table using the “primary key” constraint. The “name” column is defined as a variable character (varchar) with a maximum length of 20 characters. This table is suitable for storing information about students, where each student has a unique identifier (“id”) and a corresponding name (“name”).

Query:

CREATE TABLE Student(
    id int PRIMARY KEY,
    name varchar(20)
);

Step 3: Insert some values in Student table

The provided SQL statement inserts three records into the “Student” table. Each record consists of values for the “id” and “name” columns. The inserted data is as follows:

1. Student with id=1 and name=’Akash’
2. Student with id=2 and name=’Aman’
3. Student with id=3 and name=’Munna’

These records are added to the “Student” table, reflecting information about three different students with unique identifiers and corresponding names.

Query:

INSERT INTO Student
(id,name)
values
(1,"Akash"),
(2,"Aman"),
(3,"Munna");

Methods for Insert Row If Not Exists

There are 4 methods to use insert row if not exists:

1. Using INSERT IGNORE
2. Using ON DUPLICATE KEY UPDATE
3. Using REPLACE
4. Using NOT EXISTS with INSERT

Method 1: Using insert ignore

Query:

INSERT IGNORE INTO Student (id, name)
VALUES (2, 'Aman');

Output:

STUDENT Table

Explanation: The INSERT IGNORE statement in MySQL is used to insert rows into a table. When a duplicate key violation occurs (such as a primary key or unique constraint), instead of generating an error and aborting the entire statement, MySQL ignores the conflicting row and continues with the next row. In your example, if a student with ID 2 already exists in the Student table, the INSERT IGNORE statement will not raise an error; it will simply skip the insertion for that specific row and proceed with any other rows in the query. This can be useful when you want to insert data without worrying about duplicate entries causing the entire operation to fail.

Method 2: Using ON DUPLICATE KEY UPDATE

Query:

INSERT INTO Student (id, name)
VALUES (4, 'John')
ON DUPLICATE KEY UPDATE id = VALUES(id);

Output:

on duplicate key

Explanation: The SQL query is inserting a new record into the “Student” table with the values (4, ‘John’) for the columns “id” and “name” respectively. If there is a duplicate key conflict (i.e., if a record with the same “id” already exists), the query updates the “id” column with the specified value using the “ON DUPLICATE KEY UPDATE” clause. In this case, it sets the “id” to the value of itself using “id = VALUES(id)“, essentially performing an update if the key already exists.

Method 3: Using REPLACE

Query:

REPLACE INTO Student (id, name)
VALUES (3, 'Thala');

Output:

Replace into

Explanation: The SQL query is using the `REPLACE INTO` statement to insert a new record into the “Student” table or replace an existing one if a record with the same key (in this case, “id” is 3) already exists. It inserts the values (3, ‘Thala’) into the “id” and “name” columns, replacing any existing record with the same “id” if there is a conflict. The `REPLACE INTO` statement first attempts to insert the new record, and if a duplicate key violation occurs, it deletes the existing record with the conflicting key and inserts the new one.

Method 4: Using NOT EXISTS with INSERT

Code:

INSERT INTO Student (id, name)
SELECT 5, 'Sarah'
FROM DUAL
WHERE NOT EXISTS (
   SELECT 1
   FROM Student
   WHERE id = 5
);

Output:

SQL not exists

Explanation: This SQL query is inserting a new record into the “Student” table with the values (5, ‘Sarah’) for the columns “id” and “name” respectively. The insertion only occurs if there is no existing record in the “Student” table with the same “id” (in this case, id=5). The subquery checks for the existence of a record with id=5, and if no such record is found, the new values are inserted. The “FROM DUAL” clause is used in some database systems to provide a dummy table for the purpose of the SELECT statement.

INSERT IGNORE Statement for Insert Row If Not Exists in SQL

• Simplicity: It offers a straightforward and concise syntax without the need for additional clauses.
• Error Tolerance: Efficiently handles duplicate key conflicts by ignoring them, allowing the insertion of non-duplicate records to proceed without disruption.

Conclusion

In this article, we have covered 4 methods to implement ‘insert if not exist’ and ‘insert ignore’ method is great choice to deal with this. As discussed it is simple and error tolerant. ‘Insert if not exist’ is used if value is already present then just do not add that to remove redundancy. As we have seen ACID property in DBMS, it is one of the method that aligns with ACID property.