[Drupal] Dynamic expressions in update Query

Working with Drupal and not familiar with the database queries could be impossible. There is insert, update and delete queries for Drupal. Once I had a requirement on updating 2 fields in one update query.

Table structure is id (primary key), total_count, count. On running this update query the count should get added to total_count, reset the count field to zero.

As usual I wrote an update query, but the result wasn't as expected.

In such case, when we want to do is add an expression into the update query.

  db_update('table_name')
    ->expression('total_count', 'total_count + count')
    ->fields(array(
      'count' => 0,
      )
    )
    ->condition('count', $id, '=')
    ->execute(); 

Here, we are checking for a particular IP, we are adding its total_count and count and saving it in total_count.

In the fields just mention the value to which we want to rest the count to. Here it is zero.

Expression specifies a particular field to be updated as an expression. It usually used along with the fields, in cases, where we want to increment a particular field, in such case the expression would change like this,

  db_update('table_name')
     ->expression('total_count', 'total_count + :count', array(':count' => 1))
    ->fields(array(
      'count' => 0,
      )
    )
    ->condition('count', $id, '=')
    ->execute(); 

In this case on running this database query, we are adding one to the total_count and stored the result in total_count.

Expression comes handy when we want to make updation on 2 fields, where one depends on another. Database queries are used frequently in Drupal. It is easy to understand.