When working with MySQL databases, there are several ways to insert data into a table or update an existing record if it already exists. Three commonly used methods are INSERT IGNORE, REPLACE, and INSERT … ON DUPLICATE KEY UPDATE.
MySQL insert or update if exists with and without a primary key; In this tutorial, you will learn how to insert into a table or update if exists mysql.
Insert into a MySQL table or update if exists
There are three different methods and each of these methods has its own advantages and disadvantages, depending on the specific use case. It is important to understand the differences between these methods and choose the one that best fits your needs for inserting into the MySQL table or updating if exists:
- Using INSERT IGNORE
- Using REPLACE
- Using INSERT … ON DUPLICATE KEY UPDATE
Using INSERT IGNORE
The “INSERT IGNORE” statement in MySQL is used to insert data into a table while ignoring any duplicate entries. When a user attempts to insert data into a table using the “INSERT IGNORE” statement, MySQL will first check if a row with the same values already exists in the table. If a duplicate row is found, MySQL will ignore the insertion request and move on to the next one.
For example, our users table might contain a few records already:
mysql> SELECT * FROM users LIMIT 3; +----+-------------------------+---------------------+----------------+ | id | Name | last_name | year_of_birth | +----+-------------------------+---------------------+----------------+ | 1 | In Search of Lost Time | Marcel Proust | 1913 | | 2 | Ulysses | James Joyce | 1922 | | 3 | Don Quixote | Miguel de Cervantes | 1605 | +----+-------------------------+---------------------+----------------+ 3 rows in set (0.00 sec)
If you have a large batch of new and existing data to INSERT and part of that data contains a matching value for the id field (which is a UNIQUE PRIMARY_KEY in the table), using a basic INSERT will produce an expected error:
mysql> INSERT INTO users
(id, name, last_name, year_of_birth)
VALUES
(1, 'testing', 'hella', 1960);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
On other way, if you use INSERT IGNORE, the duplication attempt is ignored and no resulting errors occur:
mysql> INSERT IGNORE INTO users
(id, name, last_name, year_of_birth)
VALUES
(1, 'hello', 'world', 1960);
Query OK, 0 rows affected (0.00 sec)
Using REPLACE
The REPLACE statement in MySQL is similar to the INSERT statement, but it has the added functionality of checking if a row with the same primary key or unique index already exists. If it does, the existing row is first deleted and then replaced with the new values. If it doesn’t exist, a new row is inserted with the specified values.
For example, the REPLACE statement in MySQL is as follows:
mysql> REPLACE INTO users
(id, name, last_name, year_of_birth)
VALUES
(1, 'helm', 'Dr', 1960);
Query OK, 2 rows affected (0.00 sec)
The REPLACE statement will first check if a row with the same primary key or unique index already exists in the table. If it does, the existing row will be deleted and replaced with the new values. If it doesn’t exist, a new row will be inserted with the specified values.
Using INSERT … ON DUPLICATE KEY UPDATE
The INSERT … ON DUPLICATE KEY UPDATE statement is a powerful feature of MySQL that allows you to insert a new row into a table or update an existing one if it already exists. This statement is particularly useful when you want to add new records to a table or update existing ones without creating any duplicates.
For example, the INSERT … ON DUPLICATE KEY UPDATE statement is as follows:
mysql> SET @id = 1,
@name= 'dde',
@last_name = 'Proust',
@year_of_birth = 1913;
INSERT INTO books
(id, name, last_name, year_of_birth)
VALUES
(@id, @name, @last_name, @year_of_birth)
ON DUPLICATE KEY UPDATE
name = @name,
last_name = @last_name,
year_of_birth = @year_of_birth;
Notice that you are using normal UPDATE syntax (but excluding the unnecessary table name and SET keyword), and only assigning the non-UNIQUE values. Also, although unnecessary for the ON DUPLICATE KEY UPDATE method to function properly, we’ve also opted to utilize user variables so you don’t need to specify the actual values we want to INSERT or UPDATE more than once.
