32

How can I update field with query builder in Yii2? I can't find this in documentation.

Thanks!

UPD

This is the solution:

// UPDATE
$connection = Yii::$app->db;
$connection->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();
cronfy
  • 1,001
  • 1
  • 15
  • 32
arfname
  • 379
  • 1
  • 4
  • 10

6 Answers6

24

Create command can be used directly as follows : 

\Yii::$app->db->createCommand("UPDATE table SET column1=:column1, column2=:column2 WHERE id=:id")
->bindValue(':id', your_id)
->bindValue(':column1', :column1_value)
->bindValue(':column2', :column2_value)
->execute();
Kshitiz
  • 2,673
  • 1
  • 18
  • 24
  • How can I do `WHERE id=:id OR id=:id2 OR id=:id3` ..etc in a loop? Do I just construct the query string in that loop or is there a better way? – Naguib Ihab Aug 06 '18 at 07:26
  • "UPDATE table SET column1=:column1, column2=:column2 WHERE id=:id1 OR id=:id2 OR id=:id3" ->bindValue(':id1', your_id) ->bindValue(':id2', your_id) ->bindValue(':id3', your_id) – Kshitiz Aug 09 '18 at 07:33
  • and if I have 100 ids? – Naguib Ihab Aug 09 '18 at 11:03
  • I ended up construgint the query string in a loop – Naguib Ihab Aug 09 '18 at 11:04
  • you can do that, using a variable – Kshitiz Aug 17 '18 at 17:55
  • Use Batch Insert – Kshitiz Jul 12 '21 at 09:16
  • If you have that many IDs, I'd consider using another format for providing your where. I prefer "operator syntax" or, array syntax. Get an array of the IDs, then build your update like this: `Yii::$app->db->createCommand()->update('table', ['column' => 'value'], ['in', 'id', $idArray])->execute()`. You can read more here: https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder#where – darksnake747 Oct 18 '21 at 11:01
13

Query builder is for select queries only (sum, max, count too). You should use other methods - AR or raw queries (https://github.com/yiisoft/yii2/blob/master/docs/guide/db-dao.md#basic-sql-queries)

zelenin
  • 804
  • 7
  • 10
  • 1
    Since this is the accepted answer, I feel like it may be useful to point out that today (in 2021) the query builder can be used for any query. Like other answers have pointed out, you can call update() on the builder object. `Yii::$app->db->createCommand()->update('tableName', ['field' => 'value'], condition)->execute()`. The 2nd parameter is a key-value pair of the fields you want to set and the values you want to set them to. The 3rd parameter is the condition for the update and could be a simple string or an array following Yii's "where array syntax". – darksnake747 Oct 18 '21 at 10:52
12

Try like this,

 Yii::$app->db->createCommand()
        ->update('table_name', [SET_Values], 'CONDITION')
        ->execute();

For Example,

 Yii::$app->db->createCommand()
             ->update('users', ['status' => 1], 'age > 30')
             ->execute();
Adil Abbasi
  • 3,161
  • 1
  • 40
  • 35
7

Also, if you need to use the column itself in the update query, you must use yii\db\Expression.

\Yii::$app->db->createCommand()
    ->update('user', ['visits' => new \yii\db\Expression('visits + 1')], 'age > 30')
    ->execute();
Sergey Onishchenko
  • 6,943
  • 4
  • 44
  • 51
3
 Yii::$app->db->createCommand()
         ->update('customer', ['otp' => $otp_rand], ['custid' => 23])
         ->execute();

**this is the right syntax and works & tested in yii2 **

user3512810
  • 171
  • 3
1

If you have mpre then one condition then use this

$this->localdb->createCommand()
    ->update(
        $this->MYTable,
        [
            'name' => $el['new'],
            'data' => $el['data'],
        ],
        [
            'userId' => $this->user,
            'product_id' => $this->productId,
            'name' => $el['old'],
            'created' => $el['date'],
            'category' => $el['cat'],
        ]

    );
rajwa766
  • 604
  • 13
  • 31