Update queries

Update queries must always use a query builder object. Certain databases require special handling for LOB (Large OBject, such as TEXT on MySQL) and BLOB (Binary Large OBject) fields, so a layer of abstraction is required to allow individual database drivers to implement whatever special handling they require.

Update queries are started using the db_update() function as follows:

$query = db_update('node', $options);

This creates an update query object that will modify one or more records to the node table. Note that braces are not required around the table name as the query builder will handle that automatically.

The update query object uses a fluent API. That is, all methods (except execute()) return the query object itself allowing method calls to be chained. In many cases, that means the query object will not need to be saved to a variable at all.

Update queries are conceptually simple, consisting of a set of key/value pairs to set and a WHERE clause. The full structure of the WHERE clause is detailed in the section on Conditional clauses, and will only be touched on here.

The typical Update query is as follows.

/* This is a horrible example as node.status is pulled from node_revision.status table as well, updating it here will do nothing. */
$num_updated = db_update('node')
->fields(array(
'uid' => 5,
'status' => 1,
))
->condition('created', REQUEST_TIME - 3600, '>=')
->execute();

The above query will update all records in the node table created within the last hour and set their uid field to 5 and status field to 1. The fields() method takes a single associative array that specifies what fields to set to what values when the specified conditions are met. Note that unlike Insert queries, UpdateQuery::fields() only accepts an associative array. Also, the order of fields in the array and the order in which methods are called are irrelevant.

The above example is equivalent to the following query:

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

The execute() method will return the number of rows affected by the query. Note that affected is not the same as matched. In the above query, an existing record that already has a uid of 5 and status of 1 will be matched, but since the data in it does not change it will not be affected by the query and therefore not be counted in the return value. As a side effect, that makes Update queries ineffective for determining if a given record already exists.

<?php
$query = db_update('mytable');
// Conditions etc.
$affected_rows = $query->execute();
?>

To apply Where conditions:

$query = db_update('block')
->condition('module', 'my_module')
->where(
'SUBSTR(delta, 1, 14) <> :module_key',
array('module_key' => 'my_module-key_')
)
->expression('delta', "REPLACE(delta, 'my_module-other_', 'my_module-thing_')")
->execute();

To use string functions in conditions:

$query = db_update('block')
->condition('module', 'my_module')
->condition('SUBSTR(delta, 1, 14)', 'my_module-key_', '<>') // causes error.
->expression('delta', "REPLACE(delta, 'my_module-other_', 'my_module-thing_')")
->execute();