Update same table using dynamic query in drupal 7

Question

HiAll,

I want to execute below sql query using Drupal 7 PDO, But could not find a way to do,

SQL:

"UPDATE  `books`  AS a 
  JOIN  `books` b  ON a.pid = b.pid    
  SET  a. `status` = 'INACTIVE'   
  WHERE   b.`pdays` * (36400) + b.`ptime_from` > 6546"

Drupal 7:

 $query = db_update('books','pb');  
    $query->fields($update_values);   
    $query->join('books', 'pc', 'pc.pid = pb.pid');
    //$query->condition("pc.pdays" * (36400) + "pc.ptime_from",$today ,'>');
   /* $query->expression('pc.pdays', 'pdays * :daytime + pc.ptime_from', array(':daytime' => '36400'),'>')*/
    $isbn_reds = $query->execute(); 

How can we write above SQL in D7 PDO?

Answer 1

Better you break this query into two parts. The first part will fetch the value and the second will do the update.

Fetch value

$query = db_select('books', 'a');
$query->join('books', 'b', 'a.pid = b.pid');
$query->fields('b', array('pdays'));
$query->execute();
$pdays = $query->fetchAssoc();

Update

$sql = "
UPDATE books as a
  join books as b on a.pid = b.pid
  set a.status = :type1
  where " . $pdays['pdays'] . " + b.ptime_from > :type2";
$args = array(':type1' => 'INACTIVE', ':type2' => 6546);
db_query($sql, $args);

Also you cannot use join in where clause.

Answer 2

To execute clauses like that you would use a where funtion. so your code would be something like

$query = db_update('books','pb');
$query->fields($update_values);
$query->where("b.pdays * (36400) + b.ptime_from > 6546");
$isbn_reds = $query->execute();