Batch change property value for product in catalog

Vote:

I have an Episerver website with the Commerce setup. There are several products in the catalog and for some reasons I have to change the value of one property for all the products (they are a lot). What would be the best way to do it, excluding changing them by hand in the editor interface?

I'm thinking about a database query, but so far I didn't succeed on making it work.

I found out all the properties are stored in [dbo].[CatalogContentProperty] (in my case in the LongString column). I naively tried to run the following query, restricted to a specific object:

UPDATE [database-name].[dbo].[CatalogContentProperty]
SET LongString='NewValue'
WHERE MetaFieldName='PropertyName' and LongString='OldValue'
and ObjectId=IdOfTheObject

The value was correctly changed in the database, but it wasn't changed in the edit UI, even after IIS restart/reset and application rebuild/restart.

What I noticed is that if I change the alue from th edit UI it also changes the field in [dbo].[CatalogContentProperty]. Do ou know if there's something I'm missing or do you have any better idea?

Thanks :)

#206775

Aug 29, 2019 16:19

Quan Mai

Vote:

What's about writing a scheduled job to iterate over the products and update them using APIs? Much safer and not much slower ...

#206779

Aug 29, 2019 16:30

Joel Yourstone

Vote:

Hey Matteo!

Generally I'd say not to use queries directly to the databases, since those tables are not open APIs, you basically don't know how they are stored and epi can at any version change something in how they are stored without warning.

I'd say the easiest (albeit not fastest) way of doing this is just recursively content load all products in your catalog(s), make the change then save them. That way you know that you've covered all basis and your code is also (epi)futureproof. :)

#206781

Aug 29, 2019 16:31

Vote:

Thank you guys. I don't know why I didn't consider the scheduled job option which indeed is the most futureproof and dev-friendly :)

#206782

Edited, Aug 29, 2019 16:37