Insert or Update?

Ever have this situation:
[php]$res = mysql_query(“select id from table where id = $id”);
$record_exists = mysql_num_rows($res);
($record_exists) ? (update_record($id);) : (insert_record($id);) ;[/php]

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?

Comments (3)

Kit PetersFebruary 6th, 2006 at 11:40 pm

I think that replace would be faster, personally. Rather than going through and modifying some, but not all, of the data in the row, you’re just replacing the row with an entirely new one. No extra seeks to find where you should and shouldn’t write.

Ligaya TurmelleFebruary 7th, 2006 at 12:59 am

IThe problem I see with replace is if there is data in other columns that you can’t have be deleted when the new row is inserted. Unless you are willing to select the old data and rebuild the record..