1

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!

  • 1
    product name is Stored in catalog_product_entity_varchar table – Rk Rathod Aug 6 '19 at 8:11
  • yeah i figured it out , but i want to update product names with respect to sku's list i have , sku's are stored in catalog_product_flat_21 and catalog_product_flat_19 table , where in catalog_product_entity_varchar table i have the names and its enity_id , so if i want to update products names it should be wrt enitity id's possible , how its possible wrt to skus? – Hamza Mustafa Aug 6 '19 at 8:19
  • using sql query ??? you can also create custom script for that – Rk Rathod Aug 6 '19 at 8:21
  • yeah if possible – Hamza Mustafa Aug 6 '19 at 8:21
  • can you provide me sample script for that! thanks – Hamza Mustafa Aug 6 '19 at 8:22
2

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);
| improve this answer | |
  • Thank you so much bro , last question what if i want to update many product names with respect to its sku, like i have a list of almost 9k products sku's which i want to update its names , than? – Hamza Mustafa Aug 6 '19 at 8:39
  • you can get product collection with addfieltofilter('sku',array('eq'=>'your_sku')) and change name of that product – Rk Rathod Aug 6 '19 at 8:41
  • my question is that how to get sku in custom script ?? – Rk Rathod Aug 6 '19 at 8:43
  • i have list of all sku's in excel file , i would manually write it on script – Hamza Mustafa Aug 6 '19 at 8:48
  • 1
    check updated answer – Rk Rathod Aug 6 '19 at 10:39
0

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.