0

I have been working quite extensively on a zen cart ecommerce website and I have a good knowledge of php but only a basic knowledge of SQL. My question relates not to zen cart or php as I've got that covered but rather the interaction between three tables.

Table 1: inventoryexport -> columns: item_num, item_price, item_stock
Table 2: zen_products_supplier -> columns: products_id, australiait_code
Table 3: zen_products -> columns(tonnes but here are the relevant ones): products_id, products_price

Now what I wish to do is to update the "products_price" in the zen_products table with the item_price in the inventory export table by selecting all products where item_num = australiait_code.

I can select the required information with this query but I am not sure how I can then plug this directly into an update query onto the zen_products table.

SELECT zen_products_supplier.products_id, inventoryexport.item_price, inventoryexport.item_stock
FROM zen_products_supplier
INNER JOIN inventoryexport
ON inventoryexport.item_num=zen_products_supplier.australiait_code

Any help would be greatly appreciated.

nemesiswho
  • 3
  • 2
  • possible duplicate of : http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match – Sumit Gupta Oct 03 '13 at 11:24
  • @Sumit In that question there are only two tables, if I was working with only two tables this would easily be resolved however it is the added complexity of working with a third that makes this more complicated. I may also note that the columns have different names in different tables as mentioned in the question which is also different. I maybe just be wrong but I cannot see how that solution helps with my current issue. – nemesiswho Oct 03 '13 at 11:44

3 Answers3

0

Here is the good answer for your topic: MySQL - UPDATE query based on SELECT Query

Probably you'll have to create temporary table. Because there could be problems with updating the table with select from the same table.

Community
  • 1
  • 1
whyte624
  • 312
  • 1
  • 15
0

If the requirement is to update the "products_price" in the zen_products table with the total item_price in the inventory export table for all the products where item_num = australiait_code, following query will work:

update zen_products  
set products_price  = (select sum(ie.item_price) 
                       from inventoryexport ie join zen_products_supplier zps on ie.item_num = 
                       zps.australiait_code )

But if you want to update the "products_price" in the zen_products table with the total item_price in the inventory export table for all the products where item_num = australiait_code and also product id in zen_products_supplier = product id of current row being updated in zen_products table, following query will work:

update zp
set zp.products_price = (select sum(ie.item_price)                     
                     from zen_products_supplier zps  
                     join inventoryexport ie on ie.item_num = zps.australiait_code
                     where zps.products_id = zp.products_id)
from zen_products as zp

Hope this helps.

Deepshikha
  • 9,896
  • 2
  • 21
  • 21
  • I believe your second example is correct bar the fact that I don't want the total. I will see if I can work a solution from your answer though. Thank you for your help – nemesiswho Oct 03 '13 at 15:56
  • I do not currently have enough reputation to upvote your answer. My apologies. – nemesiswho Oct 04 '13 at 01:41
0

I ended solving my silly question by simply seperating the SQL into two steps.

UPDATE 
inventoryexport ie, zen_products_supplier zps 
SET 
ie.products_id=zps.products_id 
WHERE ie.item_num=zps.australiait_code;

I then execute the following:

UPDATE
zen_products zp, inventoryexport ie 
SET 
zp.products_quantity=ie.item_stock, zp.products_price=ie.item_price 
WHERE 
ie.products_id=zp.products_id;
nemesiswho
  • 3
  • 2