UPDATE database field with value from other table
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.