dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Jan 17, 2020

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


Understanding INSERT IGNORE in MySQL

Usually, if there is an error during the INSERT command execution, the error is thrown and the execution stopped.

MySQL supports something named IGNORE along with INSERT, which captures the error as a WARNING.

Consider the following

CREATE TABLE 'EmployeeDetails' (
	'Id' INT(5) NOT NULL PRIMARY KEY,
	'Name' VARCHAR(50) NOT NULL COLLATE 'utf8_bin' 
)
COLLATE='utf8_bin'
ENGINE=InnoDB
;

Here, the Id is a PRIMARY KEY and hence duplicates will not be allowed.

The query below will be successful:

INSERT IGNORE INTO EmployeeDetails (Id, Name) VALUES (1,"John")

Let us execute the below query which will result in warning:

INSERT IGNORE INTO EmployeeDetails (Id, Name) VALUES (1,"Steve") 

Warning: Duplicate entry '1' for key 'PRIMARY'

Now, the execution of the query below will display the warnings:

Query:
SHOW WARNINGS;

+------------+----------+-------------------------------------------+
| Level      | Code     | Message									|
+------------+----------+-------------------------------------------+
| Warning    | 1062     | Duplicate entry '1' for key 'PRIMARY'		|
+------------+----------+-------------------------------------------+
Sridhar MS
 
Thanks for your registration, follow us on our social networks to keep up-to-date