Last updated December 19, 2012. Created by Crell on September 18, 2008.
Edited by Elijah Lynn, kalman.hosszu, j0rd, tic2000. Log in to edit this page.
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:
<?php
$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.
<?php
/* 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();
?>
Comments
Changing existing field in DB
Hi. Is there are any way to make something like this query?
UPDATE yourtable SET column=column+1 WHERE uniqueid=yourvalueI've tryed this 'column'=>"'column+1'" and get error "Incorrect integer value"
That is because fields only
That is because fields only accepts values.
You need to use expression(), see http://api.drupal.org/api/drupal/includes--database--query.inc/function/.... See http://api.drupal.org/api/drupal/includes--file.inc/function/file_usage_... for an example (this is a merge query, but they work the same way).
LIMIT
How can LIMIT used with db_update?
How can string functions be
How can string functions be placed in conditions?
Example from a my_module.install update.
<?php$query = db_update('block')
->condition('module', 'my_module')
->condition('SUBSTR(delta, 1, 14)', 'my_module-key_', '<>') // causes error.
->fields(array('delta' => 'REPLACE(delta, "my_module-other_", "my_module-thing_")'))
->execute();
?>
Call like this
You can apply where conditions as it is. see
<?php$query = db_update('block')
->condition('module', 'my_module')
->where(
'SUBSTR(delta, 1, 14) <> :module_key',
array('module_key' => 'my_module-key_')
)
->fields(array('delta' => 'REPLACE(delta, "my_module-other_", "my_module-thing_")'))
->execute();
?>