Update SKU, Product Name, Product URL key via database

Question

I would like to ask for a help or hint, how to update SKU, Product Name, Product URL Key via database. My reference (key) is old sku and I have all data in excel sheet. I have about 600 products to be updated so I don't want to do it manually and my developer quoted me 10 hours of work for creating a code which I don't think is fair for this type of work.

I used to work with Woocommerce for Wordpress and there I usually sent tasks to database and updated what was needed. In Excel I generated task code for each row (ex. UPDATE table_name SET name='value' WHERE id='xx') and copy-past it to myPHPAdmin. I thought to create the same for Magento database and deliver this code to my developer to run in in database.

I can't find where SKU, Product Name and Product URL are stored in Magento database. Could anyone help me with this, please?

Or if you have a suggestion for any other solution, I will be happy for that as well, please.

Magento version: 2.4

Thank you very much in advance.

Matin

Answer 1

First of all, it would be better to update this programmatically by using methods created for changing / updating values.

However, if you want to work in database directly:

The data for products is in the catalog_product_entity tables. There are multiple tables for various datatypes. So varchar attribute data is catalog_product_entity_varchar etc.

In this case SKU is static and can be found in catalog_product_entity, Name and URL are varchar => catalog_product_entity_varchar.

Datatypes for the attributes can be found in the eav_attribute table.

To select the values you're asking for you can go with something like:

SELECT cpe.entity_id, cpe.sku, cpev1.value as name, cpev2.value as url_key
FROM catalog_product_entity cpe
LEFT JOIN catalog_product_entity_varchar cpev1 ON cpe.entity_id = cpev1.entity_id AND cpev1.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 4)
LEFT JOIN catalog_product_entity_varchar cpev2 ON cpe.entity_id = cpev2.entity_id AND cpev2.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'url_key' AND entity_type_id = 4);

You have to consider the entity_type_id because the eav_attribute table contains attributes for other entity types as well. Usually the entity type for products is 4, It is good to check this though. I've seen cases where it is different.

Another side note, you should take care of the url_path as well. This consists of the categories and url_key. Updating the key without the path will most likely lead to issues with you url's. Another thing to consider is that there is a table url_rewrite, which contains (how surprising), url rewrites. These rewrites should be updated as well, as it will lead to mismatches now. When setting and saving and the new url with the correct methods, this will be handled.

Again, it should be possible to update directly in database, but it is NOT recommended as it is very easy to mis something after which things break down.