We are looking to insert 2 digits in front of all of our SKU's to help make them more unique and to make them differ from everyone else's who sell the same product. Is it possible using SQL to perform this?

So for example looking to make an SKU like:


become -


Any ideas on a starting point at least? We have over 150,000 SKU's so it's not something we can manually do unfortunately.

  • Is this a new site still in development or is this a production environment? – pspahn Apr 10 '14 at 22:34
  • Production. But we have a test site area which is a full replica of the production with it's own test DB etc. – MagentoMac Apr 11 '14 at 9:30

You can run the following sql on your database to prefix all of the SKU's:

UPDATE `catalog_product_entity` SET sku = CONCAT('ZX-', sku);

You will need to reindex the flat catalog afterwards. Also if you have existing orders there will be other tables that use the old sku's.

| improve this answer | |
  • Hi Jasuten this looks fine. Will run this today in our test area and report the outcome back. I imagine existing orders won't be affected as the old SKU code will display? I'll change a few SKU's manually to check this 1st I think. Thanks for your help. – MagentoMac Apr 11 '14 at 9:38
  • I would also think that there might be problems with the "Reorder"-function, but I am not sure about this (items might use old SKUs that do no longer exist) – simonthesorcerer Apr 11 '14 at 19:32

first you haveto export the product first then change in excel then re-upload again use Magmi or Magento Import Product feature (i suggest use import/export->data profiles)

| improve this answer | |
  • I did look at this and try the export method, but it kept timing out taking 150,000 products out at once. Likewise the upload won't go up either too I fear even if the export comes out. Taking them out in chunks is feasible, but a bit time consuming and messy. Direct SQL query is probably the best method? – MagentoMac Apr 11 '14 at 9:34
  • Your method would definitely be acceptable for a store with less than 2000 products say. – MagentoMac Apr 11 '14 at 9:35

Checkout this script:
Make a CSV file with content:


upload to var/import and run through browser or better in linux shell (due to limited runtime in web)...

| improve this answer | |

I have tried to update "sku" using Jasuten answer but all of updated products stop showing in PLP and search result. So here are the queries I have used to resolve this issue.

UPDATE `shared_catalog_product_item` SET sku = CONCAT('ZX-', sku);
UPDATE `sales_order_item` SET sku = CONCAT('ZX-', sku);
UPDATE `quote_item` SET sku = CONCAT('ZX-', sku);

The reason was if you are updating sku from 'catalog_product_entity', you have to update in other tables as well if you application is fully functionality and there is already placed order against that sku. Hope this help.

| 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.