How to write Mysql Update database query using Magento 2?

Run Update SQL query in a given database table by Magento 2 in a standard way without the use of Object Manager.

You can write an update MySQL query in the ResouceModel PHP class.

When you write any custom update query, You need to create a function in the ResourceModel folder PHP file.

Base Definition:

/**
 * Updates table rows with specified data based on a WHERE clause.
 *
 * @param  mixed $table The table to update.
 * @param  array $bind Column-value pairs.
 * @param  mixed $where UPDATE WHERE clause(s).
 * @return int  The number of affected rows.
 */
public function update($table, array $bind, $where = '');

You can use direct SQL query for the update,

<?php

namespace Your\Model\ClassPath;

use Magento\Framework\App\ResourceConnection;

class UpdateQuery
{
    public function __construct(
        ResourceConnection $resource
    ) {
        $this->resource = $resource;
    }

    /**
     * Update SQL Query
     */
    public function runUpdateQuery()
    {
        $connection  = $this->resource->getConnection();
        $id = 10;

        $data = ["row_id" => "value1", "sku" => "value2"]; // Key_Value Pair
        $where = ['entity_id = ?' => (int)$id];

        $tableName = $connection->getTableName("Your_TableName");
        
        $connection->update($tableName, $data, $where);
    }
}

You can write an Update query like the above way,

The First argument is the Table Name.
The Second argument is the key-value pair of the array to update a value in the existing table.
The third argument is the where condition, You need to update a specific record using where conditions.

$id = 1;
$productIds = [1,2,3,4,5];
Use single, where conditions,
$where = [‘entity_id = ?’ => (int)$id]; Its check-in query, where entity_id = 1;

Use Multiple where conditions,
$where = [‘is_enable = ?’ => (int)$id, ‘product_id IN (?)’ => $productIds];

Above query run update query like, where is_enable = 1 and product_id in [1,2,3,4,5];

Check for other Direct SQL Query in Magento 2

Share this:

Related