Query merge

Merge queries are a special type of hybrid query. Although the syntax for them is defined in the SQL 2003 specification, virtually no database supports the standard syntax. However, most provide an alternative implementation using database-specific syntax. Drupal’s merge query builder abstracts the concept of a merge query into a structured object that can be compiled into the appropriate syntax for each database. They are sometimes referred to as "UPSERT" queries, a combination of UPDATE and INSERT.

In general terms, a merge query is a combination of an insert and an update query. If a given condition is met, such as a row with a specified primary key already existing, an update query is performed. If not, an insert query is executed. In the most common case, this is equivalent to:

if ($connection->query("SELECT COUNT(*) FROM {example} WHERE id = :id", [':id' => $id])->fetchField()) {
  // Run an update using WHERE id = $id
}
else {
  // Run an insert, inserting $id for id 
}

The actual implementation varies widely from one database to another. Note that while merge queries are conceptually atomic operations, they may or may not be truly atomic depending on the specific database implementation. For example, MySQL's implementation is a single atomic query, but the degenerate case above is not.

The most common merge query idioms are listed below.

Just set it

$connection->merge('example')
  ->key('name', $name)
  ->fields([
      'field1' => $value1,
      'field2' => $value2,
  ])
  ->execute();

In the example above, we tell the query to operate on the "example" table. We then specify one key field 'name' with the value $name. Then we specify an array of values to set.

If a row already exists where the "name" field has the value $name, then the fields field1 and field2 will be set to their respective values in that existing row. If no such row exists, one will be created where name is $name, field1 is $value1, and field2 is $value2. Thus, at the end of the query, the result is the same regardless of whether the row existed previously.

Conditional set

In some cases, you may want to set values differently depending on whether a record already exists, as determined by the key() fields. There are two ways to do this.

$connection->merge('example')
  ->insertFields([
      'field1' => $value1,
      'field2' => $value2,
  ])
  ->updateFields([
    'field1' => $alternate1,
  ])
  ->key('name', $name)
  ->execute();

The above example behaves the same as the first, except that if a record already exists and is being updated, field1 will be set to $alternate1 instead of $value1, and field2 will remain unaffected. The updateFields() method accepts either a single associative array of values or two parallel numeric arrays—one of fields, one of values—which must be in the same order.

$connection->merge('example')
  ->key('name', $name)
  ->fields([
      'field1' => $value1,
      'field2' => $value2,
  ])
  ->expression('field1', 'field1 + :inc', [':inc' => 1])
  ->execute();

In this example, if the record already exists, field1 will be set to its current value plus 1. This is useful for "counter queries", where you want to increment some counter in the database each time a certain event occurs. field2 will still be set to the same value regardless of whether the record existed.

Note that expression() can be called multiple times, once for each field that should be set to an expression if the record exists. The first parameter is the field, the second is an SQL snippet indicating the expression the field should be set to, and the optional third parameter is an array of placeholder values to be inserted into the expression.

It is also not required for a field used in expression() to already be present in fields().

Given the above API, it is possible to define queries that do not logically make sense, for instance, if a field is both ignored and set via an expression when the record exists. To minimize potential errors, the following rules apply:

• If a field is set with expression(), it takes precedence over updateFields().
• If values are given in updateFields(), only those fields will be changed if the record exists. Fields not included in updateFields() will not be affected.

Note that it is still possible to define nonsensical queries. Developers should ensure that such queries are not created, as the behavior in those cases is undefined.