dcsimg
 

Submitted by: Sridhar MS(sridhar.ms@gmail.com)

Friday Jan 17th 2020 by Sridhar MS

Language: SQL, Expertise: Intermediate: MySQL supports something named IGNORE along with INSERT, which captures the error as a WARNING. See it in action.

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'		|
+------------+----------+-------------------------------------------+
Home
Mobile Site | Full Site