function db_update

7 database.inc db_update($table, array $options = array())
8 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

Database abstraction layer
Allow the use of different database servers using the same code base.
181 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 2459
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

Posted by drclaw on

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);

?>

Posted by Josh Waihi on

here is an example of how to increment a column:

<?php
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.

Posted by anupam.akolkar on

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?

Posted by AaronELBorg on

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.';

Posted by lionslair on

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();

Posted by tseven on

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.

Posted by jkwilson on

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

Posted by Mer23 on

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

Posted by Neli on

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.

Posted by sunset_bill on

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

Posted by haggins on

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

Posted by abeal on

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?

Posted by penyaskito on

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