How to update multiple records to database in Magento 2?

While I’m going to discuss with you in a later part of this blog how to update multiple records to the database in Magento 2, I would like you to have a glimpse of our Magento services here, if you have a hard time performing such activity.

In Magento 2 platform almost there would be more changes in the database in Magento 2. So it is important to add/Update multiple records to the database in Magento 2 now it’s been a bit different from the previous version of it.

Build your Amazon-like multi-vendor marketplace website @ $249/- now!!!

           Get your free live demo now: Click here

Here In my example, I will be adding a new table in MySQL: my_custom_table with two fields:

Id: Main key, Item ID

qty: int (11), number of items

In Magento 2, almost all the records have some modifies in the database. To add/update multiple records for the database, the process goes a little different from the first edition of Magento. Of course,  here is how we do that.

How to update multiple records to the database in Magento 2?

• You can usually update multiple records in the database using a save () method of the model.

$updateData = [1 => 5, 2 =>12, 3 => 16, 4=>5];

foreach($collection as $item) {

    if(isset($updateData[$item->getId()])) {

        $item->setData('qty', $updateData[$item->getId()];

        $item->save();

    }

}

However, this is not recommended because its performance is not good. You can see the save () function is called a loop. I got another solution to solve this problem.

In my resource sample I have written the following command: /app/code/[NameSpace]/[ModuleName]/Model/ResourceModel/Custom.php

$updateData = [1 => 5, 2 =>12, 3 => 16, 4=>5];

$connection = $this->getConnection();

$conditions = [];

foreach ($updateData as $id => $qty) {

$case = $connection->quoteInto('?', $id);

$result = $connection->quoteInto('?', $qty);

$conditions[$case] = $result;

}

$value = $connection->getCaseSql('id', $conditions, 'qty');

$where = ['id IN (?)' => array_keys($updateData)];

try {   

    $connection->beginTransaction();

    $connection->update($this->getTable('my_custom_table'), ['qty' => $value], $where);

    $connection->commit();

} catch(\Exception $e) {

    $connection->rollBack();

}

• There is also one more example of adding multiple records. Im mentioning that as my custom resource sample.

/app/code/[NameSpace]/[ModuleName]/Model/ResourceModel/Custom.php

This solution is better than save () method of the model in a loop.

Read: 5 Useful Tips for Successful Magento 2 Migration

CONCLUSION

This is how you can add/update multiple records to the database in Magento 2 and make necessary changes.

Looking for the best Magento 2 migration service provider? Opt us we like to be your Magento 2, migration partner.

Contact us to move further!!!