SQL Query To Update Product Quantity With Attribute Matching Reference

[arunzaks]

Hello,

I am looking for an sql query to update 'products with attributes' matching particular reference number,

I managed to find a query with select statement. 

Looking for a query to update the stock quantity via sql,

SELECT s.quantity FROM ps_product p LEFT JOIN ps_product_attribute pa ON (p.id_product = pa.id_product) LEFT JOIN ps_stock_available s ON (p.id_product = s.id_product AND (s.id_product_attribute = pa.id_product_attribute)) WHERE pa.reference ='my reference'

I need to update s.quantity from the above statement.

Any help is really appreciated.

Thanks in advance.

 

 

[ps8modules.com]

It is better to use the Prestashop function to update.

$reference = 'xadsed';
$quantity = 67;
$query = "SELECT * FROM "._DB_PREFIX_."product_attribute WHERE reference = '".$reference."'";
$attr_info = Db::getInstance()->getRow($query);
if ($attr_info) {
  StockAvailable::updateQuantity($attr_info['id_product'], $attr_info['id_product_attribute'], $quantity);
}

 

[ps8modules.com]

In the ps_stock_available table, not only the quantity for the attribute is updated, but also for the product with the 0 attribute.
A product with an attribute of 0 is the sum of all quantities with an attribute greater than 0.

Edited September 24, 2021 by WebSoft (see edit history)