Insert into a MySQL table or update if exists

sql

on-duplicate-key-update

insert-ignore

replace-into

byNikita Barsukov·Feb 24, 2025

In MySQL, take advantage of the versatile INSERT INTO ... ON DUPLICATE KEY UPDATE feature to seamlessly insert a fresh row or to smartly modify an extant one, governed by a unique index match:

-- This is where the magic happens (Or where SQL does its job)
INSERT INTO my_table (id, data) VALUES (1, 'A')
ON DUPLICATE KEY UPDATE data = 'A';

With id having unique key status, if 1 exists, data would undergo an update to 'A'. Conversely, an absence of 1 would lead to insertion of a new row where id equals 1 and data is mapped to 'A'.

Utilizing unique constraints

The act of handling unique keys in SQL introduces an element of tactical strategy. You'll find yourself navigating through a series of gates, each requiring the right key - and in the instance of a visitor arriving with an existing key, the choice is yours to update their details meanwhile crafting a new key for incoming visitors.

Efficiently manage data insertions & updates

To maintain data integrity and efficiency, consider utilizing batch insert syntax:

-- SQL: Transforming caffeine into code since 1974
INSERT INTO my_table (id, data)
VALUES (1, 'A'), (2, 'B'), (3, 'C')
ON DUPLICATE KEY UPDATE data = VALUES(data);

This pattern minimizes database interaction, allowing for the simultaneous insertion or updating of multiple rows in one sleek move.

Error resistance with `INSERT IGNORE`

In situations where a duplicate entry should not interrupt the process, INSERT IGNORE becomes your trusty friend:

-- Ignorance can be bliss after all
INSERT IGNORE INTO my_table (id, data) VALUES (1, 'A');

Given a scenario of duplicate key, MySQL would simply skip over the potential insertion - thereby preventing an error and ensuring a smooth continuation of operation.

Exploring `REPLACE INTO`

Should you need to completely overhaul a record, REPLACE INTO is your answer:

-- Out with the old, in with the new... SQL style
REPLACE INTO my_table (id, data) VALUES (1, 'New A');

Spotting a duplicate triggers a REPLACE INTO act of deleting the extant row, followed by insertion of the new data - ensuring your database is always current.

Delving into dynamic database operations

With the ON DUPLICATE KEY UPDATE technique, dynamic referencing becomes possible. Now, even the smallest detail adjustment can prevent devastating data loss:

-- Being dynamic is easy when you're speaking SQL
INSERT INTO my_table (id, data, last_updated)
VALUES (1, 'A', NOW())
ON DUPLICATE KEY UPDATE
data = VALUES(data),
last_updated = VALUES(last_updated);

Value of last_updated is set to the current time for both newly inserted rows or updated ones, ensuring dynamic updating.

Respecting data consistency

Ensure your id column or choice of unique index is backed by a unique or primary key constraint. This empowers the insert/update commands to maintain data consistency.

Exploring the `REPLACE` mechanic

While REPLACE INTO and ON DUPLICATE KEY UPDATE may look similar, bear in mind that REPLACE INTO is heavier -- a bulldozer to a shovel. REPLACE INTO comes in handy when the entire row needs refreshing:

🗑️ Discarding the old
🆕 Furnishing with the new

The power of joint syntax

When dealing with complex conditions for updating, consider combining INSERT INTO with a set of updates. This sharper precision allows for surgical manipulation of your database.

explain-codes / Sql / Insert into a MySQL table or update if exists

Linked

Insert ... ON DUPLICATE KEY (do nothing)



Replace Into Query Syntax



Mysql ON DUPLICATE KEY UPDATE for multiple rows insert in single query



Multiple Updates in MySQL



Mysql INSERT INTO ... VALUES and SELECT

