1

We have a custom application being written that communicates with Magento, we're looking to update inventory at a certain frequency and are looking to do this through SQL communicating directly with the database... what is the query that would update the inventory given a where clause to a product field?

1 Answer 1

2

The relevant database table is named cataloginventory_stock_item.

This table has product_id which refers to catalog_product_entity.entity_id.

It also has qty.

So, if you want to update product with id 1 to have a quantity of 34, the query that you look for might be as simple as:

UPDATE cataloginventory_stock_item SET qty = 34 WHERE product_id = 1;

If you do not know the product id, but you know the sku, the query might be:

UPDATE cataloginventory_stock_item SET qty = 34 WHERE product_id IN (SELECT entity_id FROM catalog_product_entity WHERE sku = 'mysku');

That would be the basic usage.

I hope this helps.

3
  • If I have a custom field that is unique, how would I go about finding it to gather the entity_id?
    – Shay Maor
    Sep 13, 2018 at 22:54
  • Nevermind, I figure it out... grabbing the entity_id requires SELECT entity_id FROM catalog_product_entity_varchar WHERE value = [custom_field_value]
    – Shay Maor
    Sep 13, 2018 at 23:00
  • 1
    I'm glad it works for you.
    – Marjan
    Sep 14, 2018 at 6:55

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.