UPDATE database field with value from other table

Question

I'm basically trying to build a query which looks something like:

UPDATE table_a, table_b
SET table_a.value = table_b.value
WHERE table_a.id = table_b.id;

It seems that a query like this is functionally equivalent:

UPDATE table_a
INNER JOIN table_b
  ON table_a.id = table_b.id
SET table_a.value = table_b.value;

The thing is that I can't figure out how the UpdateQuery allows me to attach a second table like that. I tried just adding a ->join('table_b') to my update query object, but I'm told that method is undefined. Argh.

I've ended up cheating by using db_query(), though I'm still curions to know if there's a way to do this properly using the API.

Answer 1

Given the specific nature of your SQL statement, it would appear db_query() is the way to go. Just be sure to do it as an update hook in your .install (so you can drush updb) to implement smoothly.