Magento2 write updated query using connection & Zend format

Question

I am trying to add below SQL query in the form of Zend format using Magento connection object Can you please let me know the syntax of how to follow...

update eav_attribute_set set attribute_set_name = replace(attribute_set_name, '_src', '') where attribute_set_name LIKE '%_src%';

Here is my method

protected function execute(InputInterface $input, OutputInterface $output)
 {
 try {
 $connection = $this->resourceConnection->getConnection();
 $tableName = $connection->getTableName(self::ATTRIBUTE_SET_TABLE);
 //Add your logic here
 -----------------------------
 -----------------------------
 } catch (\Exception $e){
 $output->writeln("Something went wrong please check and try again");
 }
 }

• Jinesh: you want to write you custom query am I right?
• Nagaraju Kasa: Yes @Jinesh but can we use like Zend format.
• Jinesh: You can write query like this way $objectManager = \Magento\Framework\App\ObjectManager::getInstance(); $resource = $objectManager->get('Magento\Framework\App\ResourceConnection'); $connection = $resource->getConnection(); $tableName = $resource->getTableName('mytest'); $sql = "INSERT INTO " . $tableName . " (id, name, age, email) VALUES ('', 'Hello World', 88, '[email protected]')"; $connection->query($sql)
• Nagaraju Kasa: update eav_attribute_set set attribute_set_name = replace(attribute_set_name, '_src', '') where attribute_set_name LIKE '%_src%'
• Jinesh: you can check my answer as well as check this link(magecomp.com/blog/…)

Answer 1

Try below code

 $objectManager = \Magento\Framework\App\ObjectManager::getInstance(); // Instance of object manager
 $resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
 $connection = $resource->getConnection();
 //Update Data into table
 $sql = "update eav_attribute_set set attribute_set_name = replace(attribute_set_name, '_src', '') where attribute_set_name LIKE '%_src%';";
 $connection->query($sql);

Try another way :-

<?php
namespace Vendor\Extension\Controller\Updatequery;
use Magento\Framework\App\ResourceConnection;
use Magento\Framework\App\Action\Context;
class Index extends \Magento\Framework\App\Action\Action
{
 const QUOTE_TABLE = '[TABLE_NAME]';
 private $resourceConnection;
 public function __construct(
 Context $context,
 ResourceConnection $resourceConnection)
 {
 $this->resourceConnection = $resourceConnection;
 return parent::__construct($context);
 }
 public function execute()
 {
 $connection = $this->resourceConnection->getConnection();
 $data = ["field1"=>3,"field2"=>15]; // you can use as per your requirement
 $id = 1;
 $where = ['entity_id = ?' => (int)$id];
 $tableName = $connection->getTableName(self::QUOTE_TABLE);
 $updatedRows=$connection->update($tableName, $data, $where);
 echo "Updated Rows : ".$updatedRows;
 }
}

• Nagaraju Kasa: Thanks for your answer @jinesh but can we use same query in the form of zend format in Magento2? i am looking for that. Can u suggest me
• Jinesh: now check my answer.
• Nagaraju Kasa: Ok but here where I have to add place replace?
• Jinesh: Here $where = ['entity_id = ?' => replace()]