1

i have url_rewrite table in my magento 2.1 setup in which i have url structure like this

www.exaple.com/name-of-product.html

which is desirable , but many product have the url structure like this

www.exaple.com/name-of-product-sku-2054.html so at the end they contain product sku which is sku-2054

i need to remove all such occurrence from the database where the url is like www.exaple.com/name-of-product-sku-2054.html and what to change them to www.exaple.com/name-of-product.html

i went through url_rewrite which i believe contains these urls and found out that there are around 68k entries that would be needed to change .

enter image description here

can anyone suggest how can i do that without getting into the problem with duplicate urls also will it cause any harm if i do this by replacing the values from the column request_path from table url_rewrite . Do i need to change data from other tables also?

Improve this question

1 Answer 1

Reset to default
1

You can run this MySQL command, Backup the table url_rewrite before perform

UPDATE `url_rewrite` AS a INNER JOIN `catalog_product_entity` AS b ON b.`entity_id` = a.`entity_id` SET `request_path` = REPLACE(`request_path`, b.`sku`, '');

Improve this answer
3
  • thanks for the query @Nozomy , but i am also concerned about duplicate entries that i would need to take care of for example there are url in the database like "out-the shop-sr-201.html" , "out-the shop-sr-203.html" , "out-the shop-sr-204.html" , all these product are different but on running the above query it might thow error of duplicate urls.
    – Ritesh Kumar
    Nov 25, 2019 at 8:19
  • The easy solution is to create a php file to run the update query:
    – Nozomy Chang
    Dec 17, 2019 at 8:01
  • $conn = new mysqli($dbhost, $dbuser, $dbpass,$db); $url_rewrite = $conn->query("SELECT * FROM url_rewrite a LEFT JOIN catalog_product_entity b ON b.entity_id = a.entity_id WHERE a.entity_type = 'product'"); $data_url_rewrite = array(); while ($row = mysqli_fetch_array($url_rewrite , MYSQLI_ASSOC)) { $data_url_rewrite [] = $row; } foreach($data_url_rewrite as $url){ $new_request_path = str_replace($url['sku'], '', $url['request_path']); // check if new path doesn't exist then update request_path, }
    – Nozomy Chang
    Dec 17, 2019 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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