Another essential and useful feature MySQL follows is the UPSERT feature which is for managing the database. The word UPSERT comes from UP from UPDATE and SERT from INSERT meaning this is an essential thing to update and insert in a managing database.
To be short, it is a type of operation which is very straightforward and transparent in that it completes its job in a single snap.
Example
If a record inside a table is needed to be inserted and is already there then the alternate operation update takes place instead of showing up error this gets the thing done.
Upsert is a concept that is used in the following 3 ways
- INSERT IGNORE
- REPLACE
- INSERT ON DUPLICATE KEY UPDATE
INSERT IGNORE
- This INSERT IGNORE is the concept of inserting a value into an existing table.
- When used normally INSERT detects duplicate value where UNIQUE is assigned and throws an error abandoning the rest of the data to be inserted.
- In this, It throws a warning and performs the rest of the data insertion with the help of INSERT IGNORE.
Syntax
INSERT IGNORE INTO Table(ColumnNames)
VALUES (Values to be inserted);
Let us create a table.
CREATE TABLE upsert_example (
ID int AUTO_INCREMENT PRIMARY KEY,
Name varchar(45) DEFAULT NULL,
Alias varchar(45) NOT NULL UNIQUE,
Status varchar(25) DEFAULT NULL
);
Since it is UNIQUE assigned in the Alias column no duplicate values are allowed.
INSERT INTO upsert_example(ID, Name, Alias, Status)
VALUES (1,'Peter', 'Spiderman', 'Alive'),
(2, 'Strange', 'Doctor Strange', 'Alive'),
(3, 'Norman', 'Green Goblin', 'Deceased');
INSERT IGNORE INTO upsert_example(ID, Name, Alias, Status)
VALUES (4,'May', 'Aunt', 'Deceased'),
(5, 'Harry', 'Green Goblin', 'Alive');
Here when we try to insert this same Alias name for the table it throws warning ERROR 1062 (23000): Duplicate entry 'Green Goblin' for key 'upsert_example.Alias' when IGNORE is used and updates the rest of the data with ease.
Leaving aside the error instead of stopping the process rest of the other data gets their job done of insertion.