1

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?

3
  • why do you want to use a join? Jan 25, 2013 at 9:09
  • No issue no bound to join, Jan 25, 2013 at 11:15
  • concern is implementation of : "WHERE b.pdays * (36400) + b.ptime_from > 6546" in D7 PDO Jan 25, 2013 at 11:16

2 Answers 2

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.

1

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();
2
  • Thanks, it's works. Also we need to change : $query = db_update('books','pb'); it should be $query = db_update('books'); Update dose not take second parameter as alias, It is use for another special purpose. Jan 29, 2013 at 11:55
  • good to know that it helped... :-) Jan 29, 2013 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.