How to write mysql update query in magento 2 format?

Question

In ResourceModel model I need to execute update query.

$sql = "UPDATE 2067_ci_sessions SET data='$cisess_data' WHERE id= $id";

instead of writing direct mysql query how can i write update query in magento 2 format?. Thanks in advance for your valuable answer

public function clearMlmSession($cisess_data, $cisess_cookie)
{   

    $table = "2067_ci_sessions";
    $bind = ['id' => $cisess_cookie];

    //For user information
    //select query
    $connection = $this->getConnection();
    $select = $connection->select()->from(
        $table,
        ['data']
    )->where(
        'id = :id'
    )->limit(
        1
    );
    $cisess_data = $connection->fetchRow($select, $bind);
    //$cisess_data = decode_session_data($cisess_data);
    if (empty($cisess_data)) {
        return false;
    }
    //select query


    //Update Data into table
    $sql = "Update " . $table . " Set data = '$cisess_data' where id = '$cisess_cookie'";
    $result = $connection->exec($sql);
    //$result = $connection->exec($sql);

    print_r($result); die("debug");
}

In this function select working correctly like that i need write update, i wrote my update query directly. how to write this update query in magento 2 format?

Answer 1

OK now I get your point. It's legistimate to use update SQL.

According to the Github belongs to Marius, this is the SQL way:

$this->getConnection()->update(
    $object->getResource()->getTableName($table),
    $cisess_data,
    ['id = ?' => (int)$cisess_cookie]
);

Replace the above code after the comment //Update Data into table.

BTW @Piyush, never use Object instance :)

Answer 2

You can write like

$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); 
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$tableName = $resource->getTableName('employee'); //gives table name with 
 prefix
//Update Data into table
$sql = "Update " . $tableName . "Set emp_salary = 20000 where emp_id = 12";
$connection->query($sql);

Answer 3

Please try this:

$this->_resources = \Magento\Framework\App\ObjectManager::getInstance()
                    ->get('Magento\Framework\App\ResourceConnection');
        $connection= $this->_resources->getConnection();

// select query
$Table = $this->_resources->getTableName('table_name');
$read = "Select * FROM ". $Table;
$result = $connection->fetchAll($read);

// update query
$write = "Update " . $Table . "Set name = 'test'";
$connection->query($write);

Answer 4

The proper way:

assuming:

• $this->resourceConnection is instance of Magento\Framework\App\ResourceConnection
• $this->connection is $resourceConnection->getConnection(ResourceConnection::DEFAULT_CONNECTION)
• $cisess_data = 'THIS_IS_CISESS_DATA'
• $id = 42

Build up a special select:

        $select = $this->connection->select()
            ->from(
                false,
                ['data' => $cisess_data]
            )
            ->where('id = ?', $id);

please note that we did not select any table, we are using columns to pass thru field-value pairs, and using the conditions required to the update

translate above select as update query:

        $updateSelect = $this->connection->updateFromSelect(
            $select,
            $this->resourceConnection->getTableName('2067_ci_sessions')
        );

this will result the proper UPDATE command as per

UPDATE 2067_ci_sessions SET data='THIS_IS_CISESS_DATA' WHERE id= 42;

at this point you already can run the query

        $this->connection->query($updateSelect);

or if you like one liners:

$this->connection->query(
    $this->connection->updateFromSelect(
        $this->connection->select()
            ->from(
                false,
                ['data' => $cisess_data]
            )
            ->where('id = ?', $id),
        $this->resourceConnection->getTableName('2067_ci_sessions')
    )
);

and the reason why this is the proper way:

with simple $this->getConnection()->update() you cannot join other table to the update. With this way, you can. Just extend original select with join, be sure that you using aliases for all tables (during running updateFromSelect() as well) and you are golden

see https://github.com/magento/magento2/blob/2.3.7-p2/app/code/Magento/Cron/Model/ResourceModel/Schedule.php#L77

Answer 5

For single record

$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); 
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$tableName = $resource->getTableName('custom_table'); //gives table name with 
 prefix
//Update Data into table
$sql = "Update " . $tableName . "Set qty = '123' where id = '2'";
$connection->query($sql);

For multiple records

$objectManager = \Magento\Framework\App\ObjectManager::getInstance(); 
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();

$updateData = [1 => 7, 2 =>15, 3 => 19, 4=>3];
$insertData = [];
foreach($updateData as $id => $qty) {
$insertData[] = ['id' => $id, 'qty' => $qty];
}
try {
    $connection->beginTransaction();
    $connection->insertMultiple($this->getTable('custom_table'), $insertData);
    $connection->commit();
} catch(\Exception $e) {
    $connection->rollBack();
}

Answer 6

UPDATE cataloginventory_stock_item as a, cataloginventory_stock_status as b
SET a.qty = 0, a.is_in_stock = 0 ,use_config_manage_stock = 0,
     b.stock_status = 0
WHERE a.product_id IN (603024,603004) AND a.product_id = b.product_id