Insert or Update? 
December 29th, 2005, 12:30 am by lig
Ever have this situation:
-
($record_exists) ? (update_record($id);) : (insert_record($id);) ;
Well on the MySQL mailing list a couple of ways of handling this was pointed out. It was enlightning to me - so I thought I'd pass it on. The interesting part is that they did it with only 1 SQL statement.
Method 1
One method pointed out:
mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
-> ON DUPLICATE KEY UPDATE c=c+1;
quote from the MySQL manual page
If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.
There are of course gotcha's to watch out for so be sure to read up in the manual and I don't beleive this is portable to other DB's... but that is a slick little piece of SQL for MySQL.
Method 2
The second way they noted for handling this is with the REPLACE syntax.
Quote from the manual:
REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted.
Sweet! So which do you want to do... update or delete and insert? Anyone have any idea which is faster or uses less resources?
