Last Updated: February 25, 2016
·
6.863K
· mparker17

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();  
}