MySQL: Insert into … on duplicate key update

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!

One thought on “MySQL: Insert into … on duplicate key update

  1. Rodrigo Santa Maria

    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

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