Working with a Database in Drupal 7 - Lesson 9 - UPDATE Requests

Update queries should always use the query builder. Different databases have specific handlers for LOB (Large Object, such as TEXT in MySQL) and BLOB (Binary Large Object) fields, so an abstraction layer is required for individual database drivers to implement these specifics.

Update queries must start with the db_update() function:

By creating this update query object, we will modify one or more records in the node table. Note that curly braces are not needed—the query builder automatically handles table names.

The update query object uses the Fluent API. That means all methods (except execute()) return the query object itself, allowing method chaining. In many cases, this means the query does not need to be stored in a variable.

Update queries are straightforward, consisting of a set of key/value pairs to set, along with a set of WHERE conditions. The full structure of WHERE clauses will be explored in detail in a later lesson—we’ll just touch on it here.

A typical update query looks like this:

fields(array(
    'uid' => 5,
    'status' => 1,
  ))
  ->condition('created', REQUEST_TIME - 3600, '>=')
  ->execute();
?>

This example is equivalent to the following SQL query:

UPDATE {node} SET uid=5, status=1 WHERE created >= 1221717405;

The above example will update all records in the node table that were created within the last hour, setting their uid to 5 and status to 1. The fields() method takes an associative array specifying which fields to update and the new values to assign. Note that unlike insert queries, UpdateQuery::fields() only supports associative arrays. Also, the order of fields in the array and the order in method calls does not matter.

The execute() method returns the number of rows that were updated by the query. Note that “updated” doesn’t mean all rows that matched the WHERE condition. For example, if some nodes already have uid=5 and status=1, they may match the WHERE condition but won’t be changed since their values already match. So update queries don’t always truthfully reflect how many rows currently match the given values.