0

I need to mass replace all my store SKU values. The values I want to set are from another attribute called InternalSKU.

Attribute SKU is contained in table catalog_product_entity and the Attribute Internal SKU is contained in table catalog_product_entity_varchar.

Can you please help me to write a direct SQL query to correctly replace the attribute SKU with the values from attribute InternalSKU?

0
5

Disclaimer: never ever ever directly edit the database.

Ever.

Instead, write a script that bootstraps Magento and iterates through, updating the values via Magento ORM. This makes the change available to all areas of the application that might need to know about that SKU update:

<?php

require('app/Mage.php');
Mage::app();

$products = Mage::getModel('catalog/product')->getCollection();

foreach($products as $product){
    $newsku = $product->getSku() . '_1';
    $product->setSku($newsku);
    $product->save();
}

In this contrived example all SKUs will be updated to have a _1 appended to them. There are some caveats here:

  • This is going to take forever if you have more than, like, 10 products.
  • This is going to be deadlock prone - don't run on a production store.
  • Unless you disable index on save it will pretty much run until Magento 2 is released.

I'm feeling dangerous:

It is very easy to update all skus - it is not clear, however, from your question what values you're trying to cobble together to make a new SKU. So here are a few options of how to do this:

Append a value:

UPDATE catalog_product_entity SET sku = CONCATENATE(sku, '_1');

Append a value as a result of a subquery:

UPDATE catalog_product_entity cpe SET cpe.sku = CONCATENATE(cpe.sku,
    (
        SELECT type_id FROM catalog_product_flat_1 cpf1 WHERE cpf1.sku=cpe.sku
    )
)
6
  • Great answer. The only thing I would add is that against assumptions of topic starter SKU is not an attribute :-)
    – user487772
    Jan 21 '14 at 8:16
  • 1
    @Tim. Well technically SKU is an attribute. :) Run this on your database: select * from eav_attribute where attribute_code = 'sku' and entity_type_id = (select entity_type_id from eav_entity_type where entity_type_code = 'catalog_product')
    – Marius
    Jan 21 '14 at 8:23
  • @Marius good point. never took static backend type into consideration. at lest it's not EAV in full meaning :-)
    – user487772
    Jan 21 '14 at 8:53
  • @Tim. I agree with you, I just wanted to be the "devil's advocate" on this. :)
    – Marius
    Jan 21 '14 at 8:55
  • This didn't answer my question. I am aware that modifying the db is not recommended, however I had provided an example of a MySQL statement (that was removed from my question) I use to update pricing that could have been used as a model to achieve what I was looking for. What you have proposed does not factor the InternalSKU attribute as I had requested.
    – SR_Magento
    Jan 21 '14 at 11:32

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.