Hi I have a list of sku's of all products at my store , I want to update all the names of products I have on store via sql query with respect to it's sku .
There is a table name : catalog_product_flat_21 and catalog_product_flat_19 at my database
Just for checking I have updated the column of (name) in these both tables and refresh the caches but still name on site is not update !
Tell me if you have solution of this using sql query , Thanks!
Create Script In Magento Root Directory And Run Script :-
use Magento\Framework\App\Bootstrap;
include 'app/bootstrap.php';
$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();
$state = $objectManager->get('Magento\Framework\App\State');
$state->setAreaCode('frontend');
$productRepository = $objectManager->create('Magento\Catalog\Model\ProductRepository');
$sku = "This is Test Sku";
$product = $productRepository->get($sku); // Pass Sku
$product->setName('Set Product Name');
$productRepository->save($product);
Read CSV File in Script :-
<?php
use Magento\Framework\App\Bootstrap;
include 'app/bootstrap.php';
$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();
$state = $objectManager->get('Magento\Framework\App\State');
$state->setAreaCode('frontend');
$productRepository = $objectManager->create('Magento\Catalog\Model\ProductRepository');
$sku = "This is Test Sku";
$CSVfp = fopen("your_file.csv", "r"); //put CSV file on magento root and add your file name
if($CSVfp !== FALSE)
{
while(! feof($CSVfp))
{
$data = fgetcsv($CSVfp, 1000, ","); //here you can get sku in $data object
$product = $productRepository->get($sku); // Pass Sku
$product->setName('Set Product Name');
$productRepository->save($product);
}
}
fclose($CSVfp);
Instead of using the script, use the simple queries to achieve the updates
Create one temp table say, update_names with two column "sku, new_name and row_id"
CREATE TABLE update_names (
id INT NOT NULL AUTO_INCREMENT,
sku VARCHAR(255) NOT NULL,
new_name VARCHAR(255) NOT NULL,
row_id VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
Import the csv file into the temp table
LOAD DATA INFILE 'file_path'
INTO TABLE update_names
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
Update the temp table with row_id (its used to find the product in eav tables since name was stored in varchar table with attribute_id 73)
update update_names un join catalog_product_entity cv on cv.sku = un.sku set un.row_id = cv.row_id;
Use the below query to update all product names to the new name in the temp table
update catalog_product_entity_varchar cv join update_names un on un.row_id = cv.row_id and cv.attribute_id = 73 set cv.value = un.new_name;
Delete the temp table, once you verified the new name for the product
delete table update_names;
