Bulk Update - IN () memory limits

In my app I need a way to update many records and change active from 1 to 0; and then commit new data to replace what was “active” previously.

Sort of like a version system - e.g. the DB has 100 records that are active. The user makes changes to all 100 (I am using a JS spreadsheet plugin to handle data entry).

The previous 100 is must be updated to “inactive”
The new 100 is added.

It is possible the user only updates 20 of the 100; so what that means is those 20 rows become inactive, 20 new ones are added; and the 80 other rows are unaffected. (or 50, or 75…)

I’m using this code:

         // set old data to be inactive
          $this->MyModel->query()
                ->update()
                ->set(['active' => 0])
                ->where(['active' => 1, 'id IN' => $results])
                ->execute();

        // save new data
        $data = $this->MyModel->newEntities($newData);
        $this->MyModel->saveMany($data);

where $results is an array of the IDs of the records being replaced so the IN(... ... ...) could get quite large quite quickly.

I can’t just updateAll because there is other data in the table that shouldn’t be modified (i.e. a user can only update records belonging to them.

I am struggling to explain clearly so hopefully this makes some degree of sense.

Basically -is there a better way than using IN to update a varying amount of data?

Thanks

I’m not clear on what you think using the structure you have here will gain you over updateAll. The latter updates only the fields that you tell it to update, and the former doesn’t appear to do anything that recognizes who a record belongs to.

As for “could get quite large quite quickly”, isn’t the limit on this 100 records? That’s not large at all, and should work just fine with the IN method.

You can update bulk records like

$this->Users->updateAll(['is_active'=>0], ['user_id IN'=>[1,2,3]]);

You can add more conditions in second array

1 Like