Post by kingofcopy » Wed Jun 12, 2019 3:38 pm

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

Newbie

Posts

Joined
Mon Sep 26, 2011 10:53 pm

Post by thekrotek » Wed Jun 12, 2019 4:34 pm

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

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by kingofcopy » Wed Jun 12, 2019 4:39 pm

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 ?

Newbie

Posts

Joined
Mon Sep 26, 2011 10:53 pm

Post by thekrotek » Wed Jun 12, 2019 4:45 pm

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

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am


Post by kingofcopy » Wed Jun 12, 2019 4:51 pm

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

Newbie

Posts

Joined
Mon Sep 26, 2011 10:53 pm

Post by dparakhiya » 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

For quick, professional OpenCart support
Online
contact me on skype : dparakhiya_1
email : dipneshp@gmail.com


User avatar
New member

Posts

Joined
Fri Jul 28, 2017 1:13 pm
Location - India

Post by kingofcopy » Wed Jun 12, 2019 6:23 pm

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

Newbie

Posts

Joined
Mon Sep 26, 2011 10:53 pm

Post by thekrotek » Wed Jun 12, 2019 7:01 pm

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?

Professional OpenCart extensions, support and custom work.
Contact me via email or Skype by support@thekrotek.com


User avatar
Expert Member

Posts

Joined
Sun Jul 03, 2016 12:24 am

Who is online

Users browsing this forum: No registered users and 20 guests