Insert into a MySQL table or update if exists

In MySQL, you can use the INSERT… ON DUPLICATE KEY UPDATE statement when you want to insert or update the table. 

When we create a database, we may need to update a record or insert new records; at that time, we will use the methods to solve the problem. We can insert a new record if that doesn’t exist, or we can update the already existing records based on the values provided. 

Table of Contents:

• Methods to Insert or Update in MySQL
  • Method 1: Using INSERT … ON DUPLICATE KEY UPDATE in MySQL
  • Method 2: Using Replace in a statement in MySQL
  • Method 3: Using the INSERT IGNORE INTO statement in the MySQL server
  • Method 4: MERGE (Emulated via CTEs in MySQL)
  • Method 5: INSERT … JSON_TABLE() in MySQL
• Performance Difference between the Methods
• Conclusion

Let’s create a dataset to perform these methods to insert or update a table on a MySQL server. 

Example:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

INSERT INTO users (id, name, email)
VALUES
    (1, 'Jeffrey', '[email protected]'),
    (2, 'Sam', '[email protected]'),
    (3, 'Akaay', '[email protected]'),
    (4, 'Ben', '[email protected]'),
    (5, 'Carol', '[email protected]');
SELECT * FROM users;

Output:

This is how the table looks after executing. This works in older MySQL versions but is deprecated in MySQL 8.0+. For latest versions, you can use NEW.column_name.

Methods to Insert or Update in MySQL

To update or insert records into the table, there are methods like INSERT… ON DUPLICATE KEY UPDATE, REPLACE INTO, and INSERT IGNORE.

Method 1: Using INSERT … ON DUPLICATE KEY UPDATE in MySQL

The INSERT… ON DUPLICATE KEY UPDATE will insert a new row into a table if it doesn’t exist, or this will update the row if it exists in a table. This statement will handle both update and insert conditions, and this will also help when you don’t want to check if the records already exist or whether you want to update or insert. 

Syntax:

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3)
ON DUPLICATE KEY UPDATE
    column1 = VALUES(column1),
    column2 = VALUES(column2),
    column3 = VALUES(column3);

Explanation: ON DUPLICATE KEY UPDATE This will check whether the duplicate keys exist. If they do, the row will be updated; if not, MySQL will be asked to insert the data. 

Example:

INSERT INTO users (id, name, email)
VALUES
    (1, 'Jeffrey P', '[email protected]'),
    (3, 'Akaay N', '[email protected]')
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    email = VALUES(email);
SELECT * FROM users;

Output:

Explanation: This INSERT… ON DUPLICATE KEY UPDATE method helps you insert or update a single-line statement. It checks whether the record already exists and updates it if it doesn’t. 

Now let’s see the cases if INSERT… ON DUPLICATE KEY UPDATE is not used. 

Example for Inserting into a MYSQL table or updating if exists

If you want to insert the data into a MySQL table separately instead of using the ON DUPLICATE KEY UPDATE command, you can just check for the row; if it exists, then you can update; if not, you can insert the data. 

To check if the data exists or not:

SELECT COUNT(*) FROM users WHERE id = 1;

– -This command will check whether the row exists. If 0 returns, then we need to insert a new record. If 1 or more than 0 returns, we need to update the records. 

Case 1: If the record exists

Explanation: The output returned 1, which means that the record already exists in the database. 

Case 2: If the record does not exist 

Explanation: When the record doesn’t exist, the output will return 0, indicating that the record is not present in the database 

Suppose we are inserting records in the below example:

INSERT INTO users (id, name, email)
VALUES (6, 'omar', '[email protected]');
SELECT * FROM users;

Output:

Explanation: The INSERT INTO users (id, name, email) inserted the new records in the table. 

If the record already exists, we will update the records. 

UPDATE users
SET name = 'Jeffrey', email = '[email protected]'
WHERE id = 1;

Output:

Explanation: The code updated the existing “Jeffrey’s.” email from “[email protected]” to “[email protected].” 

Method 2: Using Replace in a statement in MySQL

The REPLACE INTO statement in MySQL will insert a new row into a table. This will delete the existing row if the row has the same records and will insert a new one. 

Syntax:

REPLACE INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Example:

REPLACE INTO users (id, name, email)
VALUES 
    (1, 'Jeffrey P', '[email protected]'),
    (3, 'Akaay N', '[email protected]');
SELECT * FROM users;

Output:

Explanation: The REPLACE INTO statement replaced the data of “Jeffrey” and “Akaay.” It deleted the previous record and replaced it with new records. 

Method 3: Using the INSERT IGNORE INTO statement in the MySQL server

The INSERT IGNORE statement in MySQL will handle the duplicates; if there is an error when a duplicate record is inserted, then the INSERT IGNORE command will not stop its execution due to the error; it will skip the error part and execute the other records. 

Syntax:

INSERT IGNORE INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Example:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

INSERT INTO users (id, name, email)
VALUES
    (1, 'Jeffrey', '[email protected]'),
    (2, 'Sam', '[email protected]'),
    (3, 'Akaay', '[email protected]');  

INSERT IGNORE INTO users (id, name, email)
VALUES
    (1, 'Jeffrey', '[email protected]'),
    (3, 'Akaay', '[email protected]'),      
    (4, 'Ben', '[email protected]'),               
    (5, 'Carol', '[email protected]');        
SELECT * FROM users;

Output:

Explanation: The records of Jeffrey, Sam, and Akaay already exist, so the INSERT IGNORE statement ignored the records that already exist as duplicates and inserted the new ones.   

Method 4: MERGE (Emulated via CTEs in MySQL)

When MySQL doesn’t natively support merge statements, we can use that with CTE (common table expression) with the insert command. 

Example:

WITH existing_user AS (
    SELECT id FROM users WHERE id = 3
)

-- Step 2: Update if exists
UPDATE users
JOIN existing_user ON users.id = existing_user.id
SET name = 'Akaay Updated', email = '[email protected]';

-- Step 3: Insert if it does not exist
INSERT INTO users (id, name, email)
SELECT 3, 'Akaay Updated', '[email protected]'
WHERE NOT EXISTS (SELECT 1 FROM users WHERE id = 3);
select * from users;

Output:

Explanation: The CTE will not be efficient in MySQL, but if we combine the CTE with an update and insert a statement, we can get the desired statement. 

Method 5: INSERT … JSON_TABLE() in MySQL

The INSERT … JSON_TABLE() in MySQL inserts JSON-formatted data into the table. It will be efficient when using the JSON data.

Example:

INSERT INTO users (id, name, email)
SELECT id, name, email
FROM JSON_TABLE(
    '[ 
        {"id": 6, "name": "Diana", "email": "[email protected]"},
        {"id": 7, "name": "Mark", "email": "[email protected]"}
    ]',
    '$[*]' COLUMNS (
        id INT PATH '$.id',
        name VARCHAR(100) PATH '$.name',
        email VARCHAR(100) PATH '$.email'
    )
) AS jt;
select * from users;

Output:

Explanation: The JSON method is also helpful in inserting a record like a normal insert command.  

Performance Difference between the Methods

Conclusion

When inserting or updating records in MySQL, there are different methods to implement it. The 

INSERT… ON DUPLICATE KEY UPDATE statement works efficiently; it will update if the record exists and insert if the record doesn’t exist in a single-line statement. We can also use the insert and update statements separately if we don’t want to use the previous statement. There is also REPLACE INTO, which replaces the existing record with a new record. The INSERT IGNORE statement will ignore the duplicate records, which makes the performance faster than other methods. If you want to update or insert the record with a simple process and to avoid checking for the existence of the records, you can use the INSERT… ON DUPLICATE KEY UPDATE statement. 

FAQs