Merge queries

Last updated February 7, 2012. Created by Crell on September 18, 2008.
Edited by jojonaloha, Island Usurper, ergonlogic, davidseth. Log in to edit this page.

Merge queries are a special type of hybrid query. Although a syntax is defined for them in the SQL 2003 specification, virtually no database supports the standard syntax. Most, however, provide some alternate implementation using a database-specific syntax. The Merge query builder in Drupal abstracts the concept of a Merge query out into a structured object that can be compiled down to the appropriate syntax for each database.

In the general sense, a Merge query is a combination of an Insert query and an Update query. If a given condition is met, such as a row with a given primary key already existing, then one query is run. If not, some other query is run. In the most common case, it is equivalent to:

The actual implementation varies widely from database to database. Note that while Merge queries are conceptually an atomic operation, they may or may not be truly atomic depending on the implementation for a specific database. The MySQL implementation is a single atomic query, for example, but the degenerate case (above) is not.

The most common idioms for Merge queries are listed below.

Just set it

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

If a row already exists in which the field "name" has the value $name, then fields field1 and field2 will be set to the corresponding values in that existing row. If such a row does not exist, one will be created in which name has the value $name, field1 has the value $value1, and field2 has the value $value2. Thus at the end of the query, the end result is the same regardless of whether or not the row already existed.

Conditional set

In some cases, you may want to set values differently depending on whether or not the record, as identified by the key() fields, already existed. There are two ways to do that.

The above example will behave the same as the first, except that if the record already exists and we are updating it field1 will be set to $alternate1 instead of $value1 and field2 will not be set to anything. The update() method accepts either a single associative array of values or two numeric arrays, one fields one values, that must be in the same order.

In this example, if the record already exists then field1 will be set to its current value plus 1. That makes it very useful for "counter queries", where you want to increment some counter in the database every time a certain event happens. field2 will still be set to the same value regardless of whether the record exists or not.

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

There is also no requirement that a field used in expression() be already present in fields().

Limited update

The updateExcept() method accepts either an array of fields or a series of fields as separate parameters. Fields specified in updateExcept() will not be affected if the record already exists. That is, if a record with name = $name already exists then field2 will be set to $value2 and field1 will be ignored completely and left at whatever is its current value, but if the record does not exist it will be set to $value 1.

Precedence

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

• If a field is set to an expression(), that takes priority over both update() and updateExcept().
• If values are specified in update(), updateExcept() is ignored.
• If values are specified in update(), only those fields will be altered if the record already exists. Fields not specified in update() will not be affected.

Note that it may still be possible to define queries that do not make sense. It is up to the developer to ensure that a nonsensical query is not specified as the behavior in that case is undefined.