How to update a record with drupal_write_record?

Question

I do not understand how to update a record with drupal_write_record.

I want to update column "vid," in table "node," with the value $value, where "nid" equals $key.

This is what I have, and it doesn't look right, nor does it work. I can't find any examples.

$table = 'node';
$record = new stdClass();
$record->vid = $value;
drupal_write_record($table, $record, array('nid' => $key));

To be clear: I want to insert $value into column "vid" where the value $key exists in column "nid"; this is all happening in table node.

Answer 1

drupal_write_record updates an entire record, not a single column. With drupal_write_record you could do this.

$node = node_load($nid);
$node->vid = $vid;
drupal_write_record('node', $node, 'nid');

But dealing with nodes, you can use node_save instead, which also handles saving fields and other data. Using node_save is recommended way of saving alterations to a node, since it also deals with revisions issues along with fields, hook notification ect. It's simple to use:

$node = node_load($nid);
$node->vid = $vid;
node_save($node);

If you want to update a single value in a datebase, you can use SQL (db_query) or the db_update function which is two ways of running SQL on your database. The db_update utilizes the Drupal query builder, and will work on any database backend the Drupal supports (now and in the furture):

db_query("UPDATE {node} SET vid = :vid WHERE nid = :nid;",
  array(':nid' => $nid, ':vid' => $vid)
);

$num_updated = db_update('node')
  ->fields(array(
    'vid' => $vid,
  ))
  ->condition('nid $nid)
  ->execute();

drupal_write_record will itself use db_update, a snippet of the code looks like this:

$query = db_update($table)->fields($fields);
  foreach ($primary_keys as $key) {
    $query->condition($key, $object->$key);
  }

Which basicly creates an update query, updating all of the fields, and using all of the values set in primary key as the condition.

Answer 2

According to drupal_write_record the operation was an UPDATE or an INSERT you need to know whether the record existed or not before committing the transaction. This is not what drupal_write_record() does. This function just checks it's arguments and issues an update or an insert accordingly. So there is really no way to tell if the function did an INSERT or an UPDATE.

The solution is using db_merge() query, it is a relatively new SQL ANSI standard that does exactly what I needed (and in the case of MySQL does it in an atomic transaction).

$return_value = db_merge('mytable')
            ->key(array('id' => $key))
            ->fields($fields)
            ->updateFields(array(
                'updatedon' => $now,
            ))
            ->execute();

The return value is an integer returning 1 for inserts and 2 for updates.