2

In a store of mine, built on Magento 2.1.x I need to perform a bulk update on product SKUs.

I am thinking about doing an UPDATE directly on the DB, on the catalog_product_entity table, since I noticed that the SKU information is stored in this table.

Is it safe doing it? The SKU information is contained in other tables?

0

You can check which tables has column with sku name using next query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%sku%'

Result should look like this:

All columns with sku column

Then you can safely update the sku in all tables, but be careful - some columns can have foreign keys. You can disable the foreign key check before update, and enable it back after:

SET foreign_key_checks = 0;
# make some changes here ...
SET foreign_key_checks = 1;

or manually check all tables before the update.


Note: this solution will affect an only columns which potentially store a sku, but magento has some columns, like conditions_serialized where product sku can be used, where you should make changes manually.

Here is example from the Shipping Suite module, where sku used in the conditions_serialized column:

example from the Shipping Suite module

To obtain a list of potentially affected columns you can use this queries:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%serializ%' ORDER BY `COLUMNS`.`TABLE_SCHEMA` DESC
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%cond%' ORDER BY `COLUMNS`.`TABLE_SCHEMA` DESC

Where '%serializ%' means part of the word serialized, '%cond%' - part of the word conditions.

Example result:

Result for the conditions

Result for the serialized

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.