Last Updated: February 25, 2016

Drupal's db_merge() function does either an UPDATE or an INSERT depending on whether the record exists.

Ever wanted to perform an UPDATE if a database row exists, or an INSERT if it does not with the same data? Turns out most SQL engines have a MERGE statement (MySQL, TSQL, Oracle) which does exactly that.

Drupal's Database API has a wrapper around the MERGE statement too, db_merge(), which will take care of the differences in syntax between your database engines.

For example, if I wanted to create or update an entry in the block table (i.e.: assign a block to a region in a theme), I would do this:

$query = db_merge('block')
 ->key(array(
 'module' => 'system',
 'delta' => 'powered-by',
 'theme' => 'bartik',
 ))
 ->fields(array(
 'status' => 1,
 'weight' => 10,
 'region' => 'footer',
 'pages' => '',
 'cache' => -1,
 ))
 ->execute();

The above statement would be equivalent to:

$exists_query = db_select('block')
 ->condition('module', 'system')
 ->condition('delta', 'powered-by')
 ->condition('theme', 'bartik')
 ->countQuery();

if ($exists_query > 0) {
 db_update('block')
 ->fields(array(
 'status' => 1,
 'weight' => 10,
 'region' => 'footer',
 'pages' => '',
 'cache' => -1,
 ))
 ->condition('module', 'system')
 ->condition('delta', 'powered-by')
 ->condition('theme', 'bartik')
 ->execute();
}
else {
 db_insert('block')
 ->fields(array(
 'module' => 'system',
 'delta' => 'powered-by',
 'theme' => 'bartik',
 'status' => 1,
 'weight' => 10,
 'region' => 'footer',
 'pages' => '',
 'cache' => -1,
 ))
 ->execute(); 
}