

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Ignore MySQL INSERT IGNORE statement and display an error if duplicate values are inserted in a table
Let us first create a table −
mysql> create table DemoTable ( FirstName varchar(100) ); Query OK, 0 rows affected (0.58 sec)
As stated in the official MySQL docs, “without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted”.
Therefore, let us now add a unique index −
mysql> alter table DemoTable add unique index(FirstName); Query OK, 0 rows affected (0.50 sec) Records: 0 Duplicates: 0 Warnings: 0
Insert some records in the table using insert command. As you can see, since we haven’t used INSERT IGNORE, an error would be visible while inserting duplicate records −
mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.32 sec) mysql> insert into DemoTable values('Robert'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('David'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Robert'); ERROR 1062 (23000): Duplicate entry 'Robert' for key 'FirstName' mysql> insert into DemoTable values('Chris'); ERROR 1062 (23000): Duplicate entry 'Chris' for key 'FirstName' mysql> insert into DemoTable values('Sam'); Query OK, 1 row affected (0.12 sec)
Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+-----------+ | FirstName | +-----------+ | Chris | | David | | Robert | | Sam | +-----------+ 4 rows in set (0.00 sec)
- Related Questions & Answers
- What is the use of MySQL IGNORE INSERT statement?
- Ignore null values in MySQL and display rest of the values
- Display an error while inserting duplicate records in a MySQL table
- Can we ignore duplicate rows in COUNT?
- Ignore first 4 values in MongoDB documents and display the next 3?
- Ignore null values in MongoDB documents
- Ignore NULL and UNDEFINED values while running a MongoDB query
- Ignore NULL values from separate tables in a single MySQL query and display count of NOT NULL records
- How to ignore an exception and proceed in Python?
- Update multiple values in a table with MySQL IF Statement
- How can we ignore the negative values return by MySQL DATEDIFF() function?
- Find duplicate column values in MySQL and display them
- How can I display only unique record from MongoDB and ignore the duplicates?
- How to insert an array of values in a MySQL table with a single INSERT?
- Select * but ignore displaying results containing a specific character in MySQL
Advertisements