Category: Models & ORM

Q: Update all records using updateAll() and SQL IN clause

I am using Cakephp 3 latest version and I want to update a gender column for multiple rows. I have studied the ORM documentation and I have no idea how to NOT use foreach loop to update multiple rows instead of one updateAll() action. This is my code:

//$user_ids is array of users' id going to be updated
foreach ($user_ids as $id) {

    $users->updateAll([
        'Users.gender' => 'm'
    ], [
        'Users.id' => $id
    ]);
}

and is equivalent to SQL:

UPDATE users as Users SET `Users.gender` = 'm' WHERE `Users.id` = 1;
UPDATE users as Users SET `Users.gender` = 'm' WHERE `Users.id` = 2;
UPDATE users as Users SET `Users.gender` = 'm' WHERE `Users.id` = 3;
UPDATE users as Users SET `Users.gender` = 'm' WHERE `Users.id` = 5;

If I have four ids to update, mySQL has to perform four times, which will generate lots of I/O.


I want to use SQL IN clause in Cakephp, like:

UPDATE users as Users SET `Users.gender` = 'm' WHERE `Users.id` IN (1,2,3,5)

Is there a better way to update all records in one query only?

SOLVED sql updateall
2 Answers

Version: 3 latest

user
achao

9

There is no need to do foreach:

//$user_ids is array of users' id going to be updated
//assume $user_ids = [1,2,3,5]

$users->updateAll([
    'Users.gender' => 'm'
], [
    'Users.id IN' => $user_ids //simply update all by IN array
]);

UPDATE users as Users SET `Users.gender` = 'm' WHERE `Users.id` IN (1,2,3,5)
Accepted by achao

Query builder solution:

$query = $users->query();
$query->update()
    ->set(['gender' => 'm'])
    ->where(['id' => $user_ids], ['id' => 'integer[]'])
    ->execute();
Kristi
Kristi

137

Created: 1 Oct '16

Last Reply: 1 Oct '16

Replies: 2

Views: 994

Votes: 3

Welcome to Aero Coding!

Aero Coding is a CakePHP-focused Q&A community for professional and enthusiast cake bakers. It's built and run by you as part of the community.


Join Now Tour

Download Cakephp

Start baking your own CakePHP application!


Cakephp All Versions