How to Insert Value in MySQL Table With Duplicate Keys Validation
-
Using the
ON DUPLICATE KEYUpdate Approach to Insert Value in MySQL Table With Duplicate Keys Validation -
Using the
REPLACEApproach to Insert Value in MySQL Table With Duplicate Keys Validation -
Using the
IGNOREApproach to Insert Value in MySQL Table With Duplicate Keys Validation
The traditional SQL’s INSERT statement does not perform input validation of its arguments/values against an existing database table. It sometimes leads to errors when duplicate keys are found during insertion.
This is handled in MySQL through an extension of INSERT with statements like ON DUPLICATE KEY UPDATE, REPLACE, and IGNORE.
To illustrate these approaches, let us create a sample database called programming_languages.
-- Here goes the definition of the database
CREATE DATABASE programming_languages;
USE programming_languages;
-- Creating a table
CREATE TABLE Details(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(25) UNIQUE, -- Making name column unique
year_released VARCHAR (5),
PRIMARY KEY(id)
);
Output:
20:22:03 CREATE DATABASE programming_languages 1 row(s) affected 0.219 sec
20:22:03 USE programming_languages 0 row(s) affected 0.000 sec
20:22:03 CREATE TABLE Details(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(25) UNIQUE, year_released VARCHAR (5),PRIMARY KEY(id)) 0 row(s) affected 0.625 sec
Now, the details table is populated with values as follows.
-- Names of popular programming languages and their release year
INSERT INTO Details (name, year_released) VALUES ('python', 1991),('c++', 1985),('Java', 1995);
SELECT * FROM Details ORDER BY id; -- Previewing table
Output:
id name year_released
1 python 1991
2 c++ 1985
3 Java 1995
-----------------------------------------------------------------------------------------
20:23:24 INSERT INTO Details (name, year_released) VALUES ('python', 1991),('c++', 1985),('Java',1995) 3 row(s) affected Records: 3 Duplicates: 0 Warnings: 0 0.109 sec
20:23:24 SELECT * FROM Details ORDER BY id LIMIT 0, 1000 3 row(s) returned 0.000 sec / 0.000 sec
We try to insert a row with the name python into the table. This operation results in an error, as expected.
-- Trying to insert a new value
INSERT INTO Details (name, year_released) VALUES ('python', 1992)
Output:
20:27:31 INSERT INTO Details (name, year_released) VALUES ('python',1992) Error Code: 1062. Duplicate entry 'python' for key 'details.name' 0.046 sec
Using the ON DUPLICATE KEY Update Approach to Insert Value in MySQL Table With Duplicate Keys Validation
This method either inserts a new value (if non-existent in a table) or updates an existing row. Hence, MySQL makes no changes if a row contains the same data as the new insert. However, it updates the row if its data is different from the insert query. If the row does not exist, it inserts the row.
INSERT INTO Details (name, year_released) VALUES ('python', 1992) as V ON DUPLICATE KEY UPDATE name = V.name, year_released = V.year_released;
SELECT * FROM Details ORDER BY id; -- Checking the output
Output:
id name year_released
1 python 1992
2 c++ 1985
3 Java 1995
-----------------------------------------------------------------------------------------
20:47:35 INSERT INTO Details (name, year_released) VALUES ('python', 1992) as V ON DUPLICATE KEY UPDATE name = V.name, year_released = V.year_released 2 row(s) affected 0.172 sec
20:49:23 SELECT * FROM Details ORDER BY id LIMIT 0, 1000 3 row(s) returned 0.000 sec / 0.000 sec
As observed from the output, the year_released column was updated for python and the log indicates that two (2) rows were affected (typical row update operation). The official reference for this extension can be consulted for extra options.
Using the REPLACE Approach to Insert Value in MySQL Table With Duplicate Keys Validation
Cautiously use this method! Unlike the previous extension, it deletes the row and inserts a new row with the desired data. While this might seem benign, issues can occur when a row has unique relationships deleted during the operation.
REPLACE INTO Details (name, year_released) VALUES ('python', 1993);
SELECT * FROM Details ORDER BY id;
Output:
id name year_released
2 c++ 1985
3 Java 1995
7 python 1993
-----------------------------------------------------------------------------------------
20:56:48 REPLACE INTO Details (name, year_released) VALUES ('python', 1993) 2 row(s) affected 0.093 sec
Now, observe that the Insert operation executes. However, the id has been altered (due to deletion and insertion). A review of the official reference for this extension is advised to determine safe usage and extra options.
Using the IGNORE Approach to Insert Value in MySQL Table With Duplicate Keys Validation
The IGNORE approach does nothing to the row with duplicate keys. It, however, does not throw an error for the INSERT operation. This method can handle cases where updates are not allowed and raising exceptions/errors is not desired.
INSERT IGNORE INTO Details (name, year_released) VALUES ('python', 1991);
SELECT * FROM Details ORDER BY id;
Output:
id name year_released
2 c++ 1985
3 Java 1995
7 python 1993
-----------------------------------------------------------------------------------------
21:29:44 INSERT IGNORE INTO Details (name, year_released) VALUES ('python', 1991) 0 row(s) affected, 1 warning(s): 1062 Duplicate entry 'python' for key 'details.name' 0.157 sec
As expected, MySQL does not raise any errors. However, the table remains unaltered.
Victor is an experienced Python Developer, Machine Learning Engineer and Technical Writer with interests across various fields of science and engineering. He is passionate about learning new technologies and skill and working on challenging problems. He enjoys teaching, intellectual discourse, and gaming, among other things.
LinkedIn GitHub