How to create Mysql Update query using Magento 2?

You can create update query using Magento 2 with Magento way.

You can write a custom update MySQL query in ResouceModel PHP file.

When you write any custom update query, You need to create a function in 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 = '');

/**

* 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
class FetchOneSql {
   public function __construct(
       \Magento\Framework\App\ResourceConnection $resource
   ) {
       $this->resource = $resource;
   }

   /**
    * Update Sql Query
    */
   public function runUpdateQuery()
   {
       $connection  = $this->resource->getConnection();
       $data = ["key1"=>"value1","key2"=>"value2"]; // Key_Value Pair
       $id = 10;
       $where = ['entity_id = ?' => (int)$id];

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

<?php

class FetchOneSql {

public function __construct(

\Magento\Framework\App\ResourceConnection $resource

) {

$this->resource = $resource;

}

/**

* Update Sql Query

public function runUpdateQuery()

{

$connection = $this->resource->getConnection();

$data = ["key1"=>"value1","key2"=>"value2"]; // Key_Value Pair

$id = 10;

$where = ['entity_id = ?' => (int)$id];

$tableName = $connection->getTableName("Your_Tablename");

$connection->update($tableName, $data, $where);

}

You can create Update query like above way,
The First argument is the Table Name.
The Second argument is key-value pair of the array to update a value in the existing table.
The third argument is 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 Post