Update stock and price SQL

[SandraNanou]

Hi,

I need to run a script to automatically update product stock and prices.

I have a CSV file containing my product references, price and quantity.

I need to update 3 tables : 

ps_product,

ps_product_shop

ps_stock_available

ps_product is easy to update as I can find the reference in my CSV file and in the table, but I have no reference in the 2 other tables.

Any idea how to achieve this?

 

Thanks,

Sandra

[SandraNanou]

I found a way to do it. I post it here in case it can help others:

$mysqlquery.="UPDATE ps_stock_available AS s
                INNER JOIN ps_product_shop AS ps ON ps.id_product = s.id_product
                INNER JOIN ps_product AS p ON p.id_product = ps.id_product
                SET s.quantity = ".$data[2].", ps.price=".$data[3]." WHERE p.reference = '".$data[1]."';";

 

[Irkeria]

after you have updated ps product you can update both other tables easily like:

 

update ps_product_shop ps 

join ps_product on ps.id_product = p.id_product

set ps.price = p.price where ps.id_shop = 1 

I have add id_shop = 1 guessing you wanna update this shop, if its other shop, change the id, or if the are couple of shops sharing price, just remove the where. 

 

And for stock available:

update ps_stock_available psa 

join ps_product p on psa.id_product = p.id_product 

set psa.quantity = p.quantity 

 

 

Againg i guess there are not combinations on your shop, otherwise be carefull with the id_product_attribute value. 

 

Hope this will  help you

 

[Ignacioa]

On 6/3/2021 at 2:17 PM, Pitxi_glass said:

Solucionado de la siguiente forma:

I put  table "psso_lanix" with reference, quantity and price, then this do the trick

UPDATE psso_product INNER JOIN psso_lanix ON psso_product.id_product = psso_lanix.id_product SET psso_product.price = psso_lanix.price, psso_product.quantity = psso_lanix.quantity;
UPDATE psso_product INNER JOIN psso_product_shop ON psso_product.id_product = psso_product_shop.id_product SET psso_product_shop.price = psso_product.price;
UPDATE psso_stock_available INNER JOIN psso_product ON psso_stock_available.id_product = psso_product.id_product SET psso_stock_available.quantity = psso_product.quantity;