MySQL Upsert: Insert or Update Automatically

Upsert is a database operation used to insert data or update it if the data already exists.

Upsert is very useful for performing insert and update operations in a single query, for example when importing a large amount of data.

In MySQL, upsert can be done by adding the ON DUPLICATE KEY UPDATE clause to an INSERT statement. For example:

INSERT INTO users
    (email, name)
VALUES
    ('[e-mail]', 'Adam'),
    ('[e-mail]', 'Samir'),
    ('[e-mail]', 'Bokek')
AS new
ON DUPLICATE KEY UPDATE
    name = new.name;

In MySQL versions below 8.0.19, AS new and new.name are not supported. In that case, use VALUES(name) instead. In MySQL versions starting above 8.0.20, VALUES(name) is deprecated.

The query above will insert three new rows. However, if an email already exists (because the email column is unique), the corresponding row will be updated instead.

To perform an upsert, two conditions must be met:

1. There must be a unique column. In the previous example, email is the unique column.
2. Each column that will be updated during the upsert must be specified in the ON DUPLICATE KEY UPDATE statement.

Example

There is a users table, structured like this:

DESC users;
-- +-------+-----------------+------+-----+---------+----------------+
-- | Field | Type            | Null | Key | Default | Extra          |
-- +-------+-----------------+------+-----+---------+----------------+
-- | id    | bigint unsigned | NO   | PRI | NULL    | auto_increment |
-- | email | varchar(100)    | NO   | UNI | NULL    |                |
-- | name  | varchar(50)     | NO   |     | NULL    |                |
-- +-------+-----------------+------+-----+---------+----------------+

In the users table, there are 3 rows:

SELECT * FROM users;
-- +----+-----------------+-------+
-- | id | email           | name  |
-- +----+-----------------+-------+
-- |  1 | [e-mail]  | Adam  |
-- |  2 | [e-mail] | Samir |
-- |  3 | [e-mail] | Bokek |
-- +----+-----------------+-------+
3 rows in set (0.00 sec)

Now, five new rows need to be inserted: two rows for new users (with new emails), and three rows for existing users (emails already exist but with different names).

If this operation is performed using a regular INSERT statement, it will throw an error, because three of the new rows already exist (based on the email).

INSERT INTO users
    (email, name)
VALUES
    ('[e-mail]', 'Adamson'),
    ('[e-mail]', 'Samirni'),
    ('[e-mail]', 'Bokek'),
    ('[e-mail]', 'Rutoptop'),
    ('[e-mail]', 'Admin');

-- ERROR 1062 (23000): Duplicate entry '[e-mail]' for key 'users.email'

To solve this, use an upsert. New users will be inserted as usual, while existing users will be updated.

INSERT INTO users
    (email, name)
VALUES
    ('[e-mail]', 'Adamson'),
    ('[e-mail]', 'Samirni'),
    ('[e-mail]', 'Bokek'),
    ('[e-mail]', 'Rutoptop'),
    ('[e-mail]', 'Admin')
AS new
ON DUPLICATE KEY UPDATE
    name = new.name;

-- Records: 5  Duplicates: 2  Warnings: 0

As a result, two new rows will be inserted, and the names of the three existing rows will be updated.

SELECT * FROM users;
-- +----+-----------------+----------+
-- | id | email           | name     |
-- +----+-----------------+----------+
-- |  1 | [e-mail]  | Adamson  |
-- |  2 | [e-mail] | Samirni  |
-- |  3 | [e-mail] | Bokek    |
-- | 24 | [e-mail] | Rutoptop |
-- | 25 | [e-mail] | Admin    |
-- +----+-----------------+----------+
-- 5 rows in set (0.00 sec)