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 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:
Error Code : 1062
Duplicate entry 'vinny' for key 1
(0 ms taken)
To avoid that, use the “on duplicate key update” syntax:
Enjoy!
bbPress Notify (No-Spam) BuddyPress Bridge Documentation The BuddyPress Bridge is a premium add-on for bbPress Notify (No-Spam) – bbpnns – that lets you integrate bbpnns
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
© UseStrict Consulting - All rights reserved
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!