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.

174 functions call db_update()

File

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

Code

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

Example #1

Posted by drclaw on October 13, 2010 at 6:08pm

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

?>

No multiple-table updates?

Posted by apotek on June 16, 2011 at 11:20pm

So I must be missing something huge, but is it really possible that there's no way to do a multiple-table UPDATE statement with this new API?

In raw SQL:

UPDATE table1 t1 JOIN table2 t2 ON t1.id=t2.id SET t1.columnA=1, t2.columnB=2 WHERE t2.name='x';

Since you can't apply the join() method on a db_update, it appears this fundamental functionality has been left out.

If you try to use join() on an update query you get this:

"UpdateQuery::join()" Call to undefined method

Evidently UPDATES with JOINs are not in SQL99 spec, so unsupport

Posted by apotek on June 16, 2011 at 11:33pm

davereid helpfully pointed out that this is not in the SQL 99 spec, so it is not supported.

Back to de-efficientizing my code, for portability.

It would be interesting to

Posted by yellowcake on November 29, 2011 at 12:08pm

It would be interesting to know how much use data platform portability really is. I cannot remember anyone porting a serious app from one db to another and I have certainly never heard that there was a wave of it, not before portability became fashionable nor since. If you must use Oracle (and that is the crunch issue here, I think) then put your cursors in stored procedures and be content that although everyone else has an easier life they are still inferior.

Increment column

Posted by Josh Waihi on October 6, 2011 at 10:35pm

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.

db_query and other database api difference

Posted by subhojit777 on February 15, 2012 at 12:30pm

i want to know that, why cant i just use db_query and write raw mysql queries inside it, rather than using db_insert or db_update which requires queries which are hard to remember.

in short, i am familiar with mysql queries and dont want to learn any new way of writing queries, so is it always feasible to use db_query?