|
|||||
 |
MySQL: Insert into … on duplicate key updateI'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 ( -- Insert some data insert into mytest VALUES 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 To avoid that, use the "on duplicate key update" syntax: insert into mytest VALUES ('vinny',33) on duplicate key update age=age+1; Enjoy! 1 comment to MySQL: Insert into … on duplicate key update |
||||
|
Copyright © 2010 use strict ;#) - All Rights Reserved |
|||||
Well, this may be very usefull when dealing with large amounts of data. MySQL supports many tricks very interesting.. Congratulations for this great post!