Schonhose

Back in the old days (like yesterday) I pretty much used the same code for checking if we should do an insert or an update of a row over and over again. I just would copy the following code:


$sql = 'SELECT `a` FROM `table` WHERE `a` = 1';
$result = mysql_query($sql);
if(mysql_num_rows($result) == 1) {
// do an SQL UPDATE
}
else {
// do an SQL INSERT
}

Nothing wrong with this code, but it seems there is a better and faster way, the ON DUPLICATE KEY UPDATE . This makes it much easier:

$sql = 'INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=3';

Prior to this you have to set one column to UNIQUE. Let’s assume we set `a` as an unique column. If MySQL tries to insert another row with `a` having the value 1 it will automatically continue to the UPDATE statement so it sets `c` = 3 where `a` = 1.

If the value for `a` doesn’t exists a new row is added. Pretty sweet stuff.