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

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?