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
Well, this may be very usefull when dealing with large amounts of data. MySQL supports many tricks very interesting.. Congratulations for this great post!