How would I go about updating qty through SQL?

Question

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?

Answer 1

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.