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
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.
-
If I have a custom field that is unique, how would I go about finding it to gather the entity_id? 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] Sep 13, 2018 at 23:00 -
1