How to do `UPDATE...SET...FROM` using knex?

Question

What's the most terse way I can express an UPDATE...SET...FROM SQL statement using knex? This is what I've got currently:

  const query =
    knex('user_subscriptions').update(subscription).toQuery() +
    knex.raw(
      ' from plans p where customer_id = ? and p.id = us.plan_id ' +
        'returning us.*, p.name',
      [customer_id]
    );

The reason I'm doing this is that I want to efficiently return a field from a related table (JOIN style) without needing a separate query.

Answer 1

As instructed in the official site: knexjs.org/#Builder-update

knex('user_subscriptions')
  .returning(['us.*', 'plans.name', 'customer_id'])
  .where({
  customer_id: '?',
  plans.id:  us.plan_id
})
  .update({
    subscription : '?
  })

Does:

update `user_subscriptions` set `subscription ` = '?' where `customer_id` = '?' and 'plans.id' = 'us.plan_id'

Returns:

[ us.*: ..., plans.name: ...,  customer_id: ... ]