• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / Using INSERT IGNORE with MySQL to prevent duplicate key errors

Using INSERT IGNORE with MySQL to prevent duplicate key errors

An error will occur when inserting a new record in MySQL if the primary key specified in the insert query already exists. Using the "IGNORE" keyword prevents errors from occuring and other queries are still able to be run.

Why?

Although you shouldn’t normally attempt to insert a record without first checking if the primary key you want to use has already been used, there may be times when this is required, such as when multiple developers need to update their own copies of a database, and a particular record may already exist in one or other of the databases.

Inserting a single record

The syntax is simple – just add "IGNORE" after "INSERT" like so:

INSERT IGNORE INTO mytable
    (primaryKey, field1, field2)
VALUES
    ('abc', 1, 2);

Inserting multiple records

When inserting mutiple records at once, any that cannot be inserting will not be, but any that can will be:

INSERT IGNORE INTO mytable
    (primaryKey, field1, field2)
VALUES
    ('abc', 1, 2),
    ('def', 3, 4),
    ('ghi', 5, 6);

In the second example above, if records already existed for ‘abc’ and ‘def’ then those two records would not be inserted, but the record for ‘ghi’ would be.

Check Out These Related posts:

  1. Randomly ordering data with MySQL with a random value column
  2. Display DNS cache on Windows
  3. Master master replication with MySQL
  4. Selectively dumping data with mysqldump

Filed Under: MySql

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap