I have about 5k products. I would like to update the sale price and its main image using a query, is this possible?

I'm looking for something like:

UPDATE products 
    sale_price = 25.00, 
    main_image = 'http://source.com/589002200.jpg' 
WHERE sku = 589002200;
  • The Magmi Mass Imported tool would be what i would use for this task – Holly Jul 31 '15 at 15:59
  • If all the prices and images are gonna be the same, go ahead and do it SQL style. Otherwise, I'd advise you to create a PHP script fetching a collection of products, then looping through them and changing the data you need to change on them then save them. It might take a wee moment, but it's far cleaner than doing it with queries. – Julien Lachal Dec 22 '15 at 9:13

You probably don't want to do this.

Given you're updating a sale price could you just use the promotions for this?

If you really want to however either use magmi as it's easier or you'll need to dig into the magento EAV database.

Price is stored on the catalog_product_entity_decimal table linked to product ID (called entity_id) and images are stored on in the catalog_product_entity_varchar table. Not sure if the attribute_id will be the same across stores or not but for images mine are 74 75 76 (for main, small and thumbnail respectively)

I would highly recommend you don't go down this route (if you're unsure), and come up with a better solution, for what you want to do.

  • thank you very much for your suggestions and better, your explanation of the database. I might have misspelled my question wrong but the price I wanted to update is the actual price I want to sell the items for. – user3704920 Jul 31 '15 at 16:52

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.