Command::update() - add ability to pass Query to UPDATE FROM

[exru]

$db = db()->createCommand("
    UPDATE     bet 
    SET        status = subquery.winr
    FROM       (:subquery) AS subquery 
    WHERE      bet.id = subquery.id
", ['subquery' => new Expression($this->leadersQuery()->select(['id', 'winr'])->createCommand()->rawSql)])->execute();

//->update(/*the truth is out there*/); needed

[SilverFire]

As a result of #10287 and Gitter talk.

@exru needs to make an UPDATE query from another query.

Assume we have the following query:

$query = new \yii\db\Query()
          ->select(['id', 'winr'])
          ->from('leaders')
          ->where(['state' => User::STATE_BLOCKED]);
// yeah, it's a simple one and we manage with it without the feature,
// but imagine that it's huge, with tons of joins, expressions in select, etc

and we need to update another table from query. The expected SQL should be like @exru posted in the header.

@exru suggests to add a chained update() to Query:

     $query->update('bet', ['status' => new Expression('subquery.winr')], 'id = subquery.id');

But I prefer to modify Command::update() to pass query in addition to the table name

     $queryBuilder->update(['bet', 'subquery' => $query], ['status' => new Expression('subquery.winr')], 'id = subquery.id');

or

     $queryBuilder->update('bet', ['status' => new Expression('subquery.winr')], 'id = subquery.id')->from(['subquery' => $query]);

@yiisoft/core-developers

[exru]

I guess be appropriate the
$db->update('table_name',['set'], ['where'], ['from'=>$query]);
or
$db->update(['table_name', 'from'=>$query],['set'], ['where'], ['params']);

[Tigrov]

• ActiveRecord::update() and ActiveRecord::updateAll() with joining tables. #853