Yii2: update field with query builder

Question

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();

@tareq - problem is that I have nothing to show because I don't know what to do. — arfname, Aug 27 '14 at 06:20

Kshitiz · Answer 1 · 2018-07-11T04:47:41.297

21

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();

edited Jul 11 '18 at 04:47

answered Aug 30 '14 at 09:15

Kshitiz

2,433
1
16
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

score 13 · Accepted Answer · answered Aug 26 '14 at 07:45

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)

answered Aug 26 '14 at 07:45

zelenin

804
7
10

Adil Abbasi · Answer 3 · 2016-08-01T06:42:10.210

9

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();

edited Aug 01 '16 at 06:42

answered Aug 01 '16 at 06:35

Adil Abbasi

2,857
1
35
32

score 7 · Answer 4 · answered Oct 06 '16 at 10:38

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();

answered Oct 06 '16 at 10:38

Sergey Onishchenko

4,740
2
34
43

score 3 · Answer 5 · answered Jun 10 '17 at 07:59

3

 Yii::$app->db->createCommand()
         ->update('customer', ['otp' => $otp_rand], ['custid' => 23])
         ->execute();

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

answered Jun 10 '17 at 07:59

user3512810

71
2

score 1 · Answer 6 · answered Mar 09 '18 at 18:47

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'],
        ]

    );

Yii2: update field with query builder

6 Answers6

Linked