Magento2 - updateFromSelect SQL #sql #magento2 #db #database

magentoUpdateFromSelectSql.php

<?php

/** Quick Example how to use updateFromSelect in Magento 2 */

$this->connection = $this->resourceConnection->getConnection();
$this->someTable = 'some_table';
$this->tmpTableName = 'tmp_table';

$condition = 'ft.' . MyInterface::CRM_ID . ' = tmp.' . MyInterface::CRM_ID;
$condition .= ' AND ft.' . MyInterface::CODE . ' = tmp.' . MyInterface::CODE;
$select = $this->connection->select()
    ->joinLeft(
        ['tmp' => $this->tmpTableName],
        $condition,
        [
            MyInterface::MYFIELD  => 'tmp.' . MyInterface::MYFIELD,
            MyInterface::NAME     => 'tmp.' . MyInterface::NAME,
            MyInterface::DATE     => 'tmp.' . MyInterface::DATE,
        ]
    )
    ->where('tmp.mvt = ?', 2);

$sql = $this->connection->updateFromSelect($select, ['ft' => $this->someTable]);

/** @var \Zend_Db_Statement_Interface $results */
$results = $this->connection->query($sql);
        

pureQuery.sql

UPDATE `some_table` AS `ft`
LEFT JOIN `tmp_table` AS `tmp` 
  ON ft.crm_id = tmp.crm_id 
  AND ft.code = tmp.code
SET 
  `ft`.`myfield` = `tmp`.`myfield`,
  `ft`.`cli_eligible` = `tmp`.`name`,
  `ft`.`date` = `tmp`.`date`
WHERE (tmp.mvt = 2)