MySQL: Insert into … on duplicate key update

Handy syntax to work around primary key violations

Share This Post

Share on facebook
Share on linkedin
Share on twitter
Share on email

I’m currently working on a project and came across the need to decide between inserting or updating a record in a MySQL table using PHP. The first thing that came to mind was to do a select and do the insert if my key wasn’t there, otherwise, do the update.

That’s when I found the “on duplicate key update” for inserts. It basically does the if/else logic in one clean SQL sweep. This is how it works:


-- Create a table and set a unique or primary key:

create table mytest (
    uname varchar(15) primary key  not null,
    age int(3)
);

-- Insert some data

insert into mytest VALUES
    ('vinny',32),
    ('vinny2', 23);

Now if you try to insert another record having uname either ‘vinny’ or ‘vinny2’, you’ll get the following error:


insert into mytest VALUES ('vinny',33);

Error Code : 1062
Duplicate entry 'vinny' for key 1
(0 ms taken)

To avoid that, use the “on duplicate key update” syntax:


insert into mytest VALUES ('vinny',33)
on duplicate key update age=age+1;

Enjoy!

More To Explore

Documentation
vinny

bbPress Notify (No-Spam) Documentation

bbPress Notify (No-Spam) Documentation The bbPress Notify (No-Spam) is a free plugin that lets you control who gets notifications of new Topics and Replies in

One Response

  1. Well, this may be very usefull when dealing with large amounts of data. MySQL supports many tricks very interesting.. Congratulations for this great post!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.