OpenCart Community

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

Update product set weight = '123' where category_id = 1

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 ?

Ah, yeah, just LEFT JOIN product_to_category table ON product_id. Completely forgot about it, sorry.

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
==============================================

change '1111' to your category_id

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

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?