In this post, we will learn the difference between INSERT IGNORE” vs “INSERT ON DUPLICATE KEY UPDATE” where both of them are used for inserting the data in a table with a slit difference that is in case of insert ignore we ignore the record which violates the rule or contain the table, for example, the key says we can not enter the duplicate value and if we try to enter a duplicate value.
Where the record which contains the duplicate value of even one column will be sideline or ignored by the compiler by itself and the data of that row would not be entered as it does not fulfill the requirements of following the values in a predefined table.
Different Insert Ways
“INSERT IGNORE” which is nothing but an advanced feature of insert used to ignore the error and skip the insertion of the row that violates the constraint. If the new row violates the unique key constraint, MySQL will not insert the row and will issue a warning instead. If the row doesn’t violate the constraint, MySQL will insert the row as usual without any changes.
And if we talk about, “INSERT ON DUPLICATE KEY UPDATE” is used to update the existing row when a new row violates a unique key constraint. If the new row violates the constraint, MySQL will update the existing row with the new values instead of inserting a new row. If the row doesn’t violate the constraint, MySQL will insert the row as usual.
We have taken an example of the same here below just follow it to get it done as per our choice where we want to restrict the data enter or inserting new data to the table.
INSERT IGNORE INTO users (id, name, email) VALUES (1, 'John', 'john@example.com');
And for making certain changes in the data or entering some duplicate values now
INSERT INTO users (id, name, email) VALUES (1, 'John', 'john@example.com') ON DUPLICATE KEY UPDATE name=' John Smith', email=' john.smith@example. com';
Here If the row which has id as 1 already exists which we entered before, this query will update the existing row with the new values for “name” and “email”. If the row doesn’t exist, MySQL will insert the new row with the specified values for “id”, “name”, and “email”.
To learn more Difference between INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE” Visit: by Stack Overflow.
To learn more about MySQL tutorials and the solutions of the problems along with concepts and tutorials of solutions list and learn visit: MySQL Tutorials And Problems.
