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 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. Required fields are marked *

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