‘insert if not exists’ in MySQL

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.

Oracle-Gives-MySQL-Workbench-a-new-Look_394x296

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);
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s