Page 1 of 1

update product weight in specific category

Posted: Wed Jun 12, 2019 3:38 pm
by kingofcopy
Hello
i have a little situation in my eshop.
So
every night run a cron job to import and update my products with xml file provided to me from my distributor. Everything works fine but i need after the import to update weight on products for the specific categories. It's easy to do this with sql query and how?
Thanks

Re: update product weight in specific category

Posted: Wed Jun 12, 2019 4:34 pm
by thekrotek
Update product set weight = '123' where category_id = 1

Re: update product weight in specific category

Posted: Wed Jun 12, 2019 4:39 pm
by kingofcopy
thekrotek wrote:
Wed Jun 12, 2019 4:34 pm
Update product set weight = '123' where category_id = 1
Hello my friend
i try it but with no luck ... see below
======================================================
SQL query:


Update oc_product set weight = '2' where category_id = 4854
MySQL said: Documentation

#1054 - Unknown column 'category_id' in 'where clause'
=====================================================

maybe because category_id is in other table ?

Re: update product weight in specific category

Posted: Wed Jun 12, 2019 4:45 pm
by thekrotek
Ah, yeah, just LEFT JOIN product_to_category table ON product_id. Completely forgot about it, sorry.

Re: update product weight in specific category

Posted: Wed Jun 12, 2019 4:51 pm
by kingofcopy
thekrotek wrote:
Wed Jun 12, 2019 4:45 pm
Ah, yeah, just LEFT JOIN product_to_category table ON product_id. Completely forgot about it, sorry.
maybe write something wrong ?

======================================
SQL query:


Update oc_product set weight = '2' LEFT JOIN oc_product_to_category ON product_id = 4854
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LEFT JOIN oc_product_to_category ON product_id = 4854' at line 1
==============================================

Re: update product weight in specific category

Posted: Wed Jun 12, 2019 5:51 pm
by dparakhiya

Code: Select all

UPDATE oc_product as p SET p.weight = '2' WHERE p.product_id in (SELECT pc.product_id FROM oc_product_to_category as pc WHERE pc.category_id = 1111);
change '1111' to your category_id

Re: update product weight in specific category

Posted: Wed Jun 12, 2019 6:23 pm
by kingofcopy
dparakhiya wrote:
Wed Jun 12, 2019 5:51 pm

Code: Select all

UPDATE oc_product as p SET p.weight = '2' WHERE p.product_id in (SELECT pc.product_id FROM oc_product_to_category as pc WHERE pc.category_id = 1111);
change '1111' to your category_id
Thanks my friend works ;D ;D :D :D

Re: update product weight in specific category

Posted: Wed Jun 12, 2019 7:01 pm
by thekrotek
dparakhiya wrote:
Wed Jun 12, 2019 5:51 pm

Code: Select all

UPDATE oc_product as p SET p.weight = '2' WHERE p.product_id in (SELECT pc.product_id FROM oc_product_to_category as pc WHERE pc.category_id = 1111);
change '1111' to your category_id
You know that LEFT JOIN can be used in UPDATE, right?