8.3.x database.inc db_update($table, array $options = [])
8.0.x database.inc db_update($table, array $options = array())
8.1.x database.inc db_update($table, array $options = array())
8.2.x database.inc db_update($table, array $options = array())
8.4.x database.inc db_update($table, array $options = [])
7.x database.inc db_update($table, array $options = array())

Returns a new UpdateQuery object for the active database.

Parameters

$table: The table to update.

$options: An array of options to control how the query operates.

Return value

UpdateQuery A new UpdateQuery object for this connection.

Related topics

182 calls to db_update()
actions_loop_test_install in modules/simpletest/tests/actions_loop_test.install
Implements hook_install().
AggregatorCronTestCase::testCron in modules/aggregator/aggregator.test
Adds feeds and updates them via cron process.
aggregator_block_save in modules/aggregator/aggregator.module
Implements hook_block_save().
aggregator_cron in modules/aggregator/aggregator.module
Implements hook_cron().
aggregator_refresh in modules/aggregator/aggregator.module
Checks a news feed for new items.

... See full list

File

includes/database/database.inc, line 2518
Core systems for the database layer.

Code

function db_update($table, array $options = array()) {
  if (empty($options['target']) || $options['target'] == 'slave') {
    $options['target'] = 'default';
  }
  return Database::getConnection($options['target'])->update($table, $options);
}

Comments

drclaw’s picture

A simple example of this function borrowed from http://drupal.org/node/310080 Go there for more information!

<?php
// For the following query:
// UPDATE {node} SET uid=5, status=1 WHERE created >= 1221717405

$num_updated = db_update('node') // Table name no longer needs {}
  ->fields(array(
    'uid' => 5,
    'status' => 1,
  ))
  ->condition('created', REQUEST_TIME - 3600, '>=')
  ->execute();

// Above Example is Equivalent to the Following in D6
$result = db_query("UPDATE {node} SET uid = %d, status = %d WHERE created >= %d", 5, 1, time() - 3600);

?>
Josh Waihi’s picture

here is an example of how to increment a column:

db_update('system')
    ->expression('weight', 'weight + :weight', array(':weight' => 1))
    ->condition('name', 'mymodule')
    ->condition('type', 'module')
    ->execute();

You can't use ::fields() as PDO takes the value as literal.

anupam.akolkar’s picture

Want to run query like:

update table_name set a=2 where b in (select bcol from table_name2)

Is this possible with db_update -- can my where clause contain a condition like used above?

AaronELBorg’s picture

Hey, anupam.akolkar.

You probably already figured this out but in the interest of helping others, here's how to do it. You'll have to do the "(select bcol from table_name2)" in a subquery.

Here's mine. I had to do it to turn comments on for node revisions.

$subquery = db_select('node');
$subquery->addField('node', 'nid');
$subquery->condition('node.type', 'YOUR_NODE_TYPE', '=');

$num_updated = db_update('node_revision')
->fields(array('comment' => '2',))
->condition ('node_revision.nid', $subquery, 'IN')
->execute();

echo $num_updated . ' nodes have been updated.';
lionslair’s picture

Can anyone tell me why either of these two blocks do not work

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'replace(path, "sites/local.thetrustedtrolley.com.au/", "sites/lo' for key 'PRIMARY': UPDATE {menu_router} SET path=:db_update_placeholder_0, tab_root=:db_update_placeholder_1; Array ( [:db_update_placeholder_0] => replace(path, "sites/local.thetrustedtrolley.com.au/", "sites/local.thetrustedtrolley.com.au/") [:db_update_placeholder_1] => replace(tab_root, "sites/local.thetrustedtrolley.com.au/", "sites/local.thetrustedtrolley.com.au/") )

            db_update('menu_router')->fields(array(
                'path' => 'replace(path, "'.$old_site.'", "'.$current_site.'")',
                'tab_root' => 'replace(tab_root, "'.$old_site.'", "'.$current_site.'")',
            ))->execute();

PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column ':old_site' in 'field list': UPDATE {menu_router} SET path=replace(path, ":old_site", ":current_site"), tab_root=replace(tab_root, ":old_site", ":current_site"); Array ( [:old_site] => sites/local.thetrustedtrolley.com.au/ [:current_site] => sites/local.thetrustedtrolley.com.au/ )

            db_update('menu_router')
            ->expression('path', 'replace(path, ":old_site", ":current_site")', array(':old_site' => $old_site, ':current_site' => $current_site))
            ->expression('tab_root', 'replace(tab_root, ":old_site", ":current_site")', array(':old_site' => $old_site, ':current_site' => $current_site))
            ->execute();
sludwig’s picture

your second block would work correctly if you remove the quotation marks around then variables like this:

db_update('menu_router')
  ->expression('path', 'replace(path, :old_site, :current_site)', array(':old_site' => $old_site, ':current_site' => $current_site))
  ->expression('tab_root', 'replace(tab_root, :old_site, :current_site)', array(':old_site' => $old_site, ':current_site' => $current_site))
  ->execute();
tseven’s picture

Is it possible to specify the max number of rows to update?

I'd imagine it is, but I'm not familiar enough with PDO or how drupaly 7 implements it to figure it out.

jkwilson’s picture

You can do this with a dynamic query, using the range method. Hope that helps.

Mer23’s picture

I have the following queries to fetch a single record from mysql database
$sql1 = "SELECT bid FROM {block} WHERE title = :title AND region = :region AND theme= :theme LIMIT 1" ;
$result1 = db_query($sql1, array(':title' => 'Free Alternative',':region'=> '-1',':theme'=>'bartik'));

I want to use the variable,$result1 in where condition of db_update.How do I achieve this?
db_update('block')
->fields(array('region' , 'sidebar_first'))
->condition('bid',$result1)
->execute();
throws the error:
Recoverable fatal error: Object of class DatabaseStatementBase could not be converted to string in DatabaseStatementBase->execute()
mysql_fetch_array($result1) doesn't work too

Thank You

Neli’s picture

If the outcome of $result1 is exactly the same as the stated condition in the 'bid' column (I haven't tested it), your second query should be something like this:

db_update('block')
->fields(array('region' => 'sidebar_first'))
->condition('bid', $result1, '=')
->execute();

Hope this helps.

sunset_bill’s picture

I don't get any kind of error, it's just behaving very oddly. my code:

    $query = db_update($field_data_table)
               ->fields(array($anr_field => $ref_node->nid))
               ->condition('entity_id', $entity->nid);
    $rows = $query->execute();
/* dpm of variables in order used:
field_data_field_test_anr
field_test_anr_anr_reference
136
135
*/

What's really weird is that if I take out the condition, it updates the field in every row *but* the one I'm actually trying to update.

thanks,
SB

haggins’s picture

Is it possible to use db_update() to update multiple rows to different values at once like described here?

ionmedia’s picture

yes, it is possible!

$transaction=3; // it can be up to 50 000 rows in one transaction, fantastic speed!!!
$exression='CASE';
for ($i = 0; $i condition('node.nid', $nidarray, 'IN');
$vidupdatequery->expression('vid', $exression);
$vidupdatefirstnid=$vidupdatequery->execute();

sherakama’s picture

This is great.
For those that want the value to be a string and not a number you will need one more trick to make it work.
The expression will need new lines after each then value.

eg:

$expression .= " WHEN nid = $nid THEN '" . $some_string_value . "'\n";
apotek’s picture

I think it's important to remember that if you are developing a module you want to share or contribute, using Expression might not be the best idea, unless you are sure that expression is supported by all the database server types that drupal officially supports. If your expression is specific to mysql, this will break when a user tries to run your module on postgres or other db.

Anonymous’s picture

So, the fact that I'm using db_update instead of db_insert would seem to indicate that I'm aware that an entry already exists, and yet, I'm getting an "Integrity constraint violation: 1062 Duplicate entry" when I try to execute a particular db_update call in a .profile callback function for a distribution I'm working on.

I don't see anything in the documentation for db_update about dealing with this issue. I'm kind of surprised it is an issue. Can anyone clarify how to use db_update when you know your entry exists already?

penyaskito’s picture

That means that you have another row with the same unique keys. If you need further help, ask for support.

samsterlin’s picture

 $num_updated = db_update('node') // Table name no longer needs {}
  ->fields(array(
    'uid' => 5,
    'status' => 1,
  ))
  ->condition('created', REQUEST_TIME - 3600, '>=')
  ->execute(); 
sgurlt’s picture

Can someone please explain me how to write a db_update for a BLOB field?

I need to update the "Options" field in the "Menu_Links" Table, but dont know the correct syntax for it :-/

slpcc63’s picture

I have a form for users to update labels for cameras that my Drupal 7 module is displaying. When the user submits the form, first I check to see if the submitted value already exists int he DB:

$query = db_select('floormap_cameras', 't')
->fields('t')
->condition('camera_name', $_POST['camera_update'][1], '=')
->execute()->fetchAll();

Then, if the value doesn't return any rows, I update the 'camera_name' field for the matching address:

if (empty($query[0]->camera_name)) {
try {
$update = db_update('floormap_cameras')
->fields(array(
'camera_name' => $_POST['camera_update'][1],
))
->condition('address', $_POST['camera_update'][0], '=')
->execute();
}
catch(Exception $e) {
echo $e->getMessage() . '
';
}

no matter what I put in for the new label, I get an error saying:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'Shannon6' for key 'camera_name'

I get this even though nothing I attempt to enter is already in the table. The update shouldn't even run if the value is being used, I'm only running it if the first query returns nothing matching the label.

webgeer’s picture

I'm trying to figure out how to do this Query:

UPDATE mytable AS m JOIN node AS n ON m.nid= n.nid SET m.activated=n.created WHERE n.type = 'mytype'
blasthaus’s picture

Here's how to use CONCAT.

$updated = db_update('table_name')
  ->expression('column_name', 'CONCAT(:prepend, column_name)', array(
      ':prepend' => 'string to prepend ', 
    ))
  ->condition('id', $id)
  ->execute();
pit1988’s picture

Hi, I want to update a table only of a checked elements (with checkboxes)
$check = array_filter($form_state['values']['table']);

//dsm($check);

$sql = db_update('field_data_field_state');
$sql->fields(array('field_state_value'=> 'ok'));
$sql->condition('???????????????', $check, 'IN');
$sql->execute();

dsm ($check) returns only the positions of checked elements (integers) what should i put on '??????' entity_id doesn't work