10

Is there any way to db_update() for the following query?

UPDATE field_data_field_TEST as ft 
left join node as n on ft.entity_id = n.nid
set n.type='test'
where n.type='foo'

I tried using db_update()->join(); but it didn't work.

12

db_update() doesn't implement any interfaces that have join()/innerJoin()/etc. methods so I think you're stuck with using db_query() and writing the query string out manually.

$sql = "
  UPDATE field_data_field_TEST as ft 
  left join node as n on ft.entity_id = n.nid
  set n.type = :type1
  where n.type = :type2";

$args = array(':type1' => 'test', ':type2' => 'foo');
db_query($sql, $args);
  • it is a workaround but in the drupal documentation they say "Do not use this function for INSERT, UPDATE, or DELETE queries. Those should be handled via db_insert(), db_update() and db_delete()respectively." so i think maybe to implement a subquery would be less performance but more correctly. What do you think ? – ivan Dec 20 '12 at 12:08
  • 2
    I'd take that documentation with a pinch of salt...if DBTNG can't perform the query you need with an existing class, it's perfectly acceptable to use db_query(). If you want to stick rigidly to the documentation then sure, a condition with a subquery would be an option. But as you say, it would be less performant and to me doesn't fell any 'better' than using a straight sql query – Clive Dec 20 '12 at 12:19
  • Just noting this appears to be also the case in Drupal 8, Update doesn't implement any join functions, still need to use the generic query for this case. – David Thomas Jan 10 at 22:22

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.