Changing multiple reference numbers ?

[zsocialmedia]

Hello! I have this situation. A store with 50k products splitted in different categories. What I want to do is. To add a prefix to reference numbers. I mean..to add prefix to reference number of products from a specific category. anyone able to suggest me one addon or... how i can edit this from MYSQL ? thank you so much

[Teemu Mäntynen]

For example to add prefix "abc-" in front of the reference code for products that have category 2 as their default category:

UPDATE `ps_product` SET `reference`=CONCAT("abc-", `reference`) WHERE `id_category_default` = 2

Please be careful. I have not tested this on a production server. Take good care of backups. Preferably first test on a separate development server.

[Constantino]

there's a possibility to do it via export/import with Store Manager (there's a free trial) - filter products be selected category, export 2 columns: ID and reference to file, make modification manually or use and expression at the step of import

[Guest]

2 hours ago, Teemu Mäntynen said:

For example to add prefix "abc-" in front of the reference code for products that have category 2 as their default category:

UPDATE `ps_product` SET `reference`=CONCAT("abc-", `reference`) WHERE `id_category_default` = 2

Please be careful. I have not tested this on a production server. Take good care of backups. Preferably first test on a separate development server.

or multiple:

2,3,4,5 is id category

(NOT TESTED)

UPDATE ps_product set reference = CASE id_category_default 
                                     WHEN 2 THEN CONCAT('abc-',reference)
                                     WHEN 3 THEN CONCAT('def-',reference)
                                     WHEN 4 THEN CONCAT('ghi-',reference)
                                     WHEN 5 THEN CONCAT('jkl-',reference)
                                     ELSE reference
                                     END
                                     WHERE id_category_default IN (2,3,4,5)

 

[zsocialmedia]

Thank you so much @Teemu Mäntynen  and @Guest

Worked perfectly