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