Update rows with the sums of fields from other table

[hiprakhar]

Hi,

 

I have to update tempqty of all available products in the productlist. For this I have to calculate the difference of the Sums of each product purchased and sold.

 

I am writing this query for the same in MySql and MS access but they are not working. In Ms Access it says "must be an updateable query", in MySQL the sum of all the qty is update in the tempqty. This is the query.

 

UPDATE CopyProductBatch AS p SET p.tempqty =

(SELECT (a.total - b.total) FROM

(SELECT SUM(qty) AS total FROM Inventory AS i, CopyProductBatch AS p WHERE type = 'p' AND i.productbatchcode = p.productbatchcode) a,

(SELECT SUM(qty) AS total FROM Inventory AS i, CopyProductBatch AS p WHERE type = 's' AND i.productbatchcode = p.productbatchcode) b)

 

Please help

[Mchl]

For MySQL

 

UPDATE CopyProductBatch AS p 
LEFT JOIN (SELECT productbatchcode, SUM(qty) AS total FROM Inventory WHERE type = 'p' GROUP BY productbatchcode) AS a USING (productbatchcode)
LEFT JOIN (SELECT productbatchcode, SUM(qty) AS total FROM Inventory WHERE type = 's' GROUP BY productbatchcode) AS b USING (productbatchcode)
SET p.tempqty = IFNULL(a.total,0) - IFNULL(b.total,0)

[hiprakhar]

Thanks Mchl!!

 

The query works perfect in MySQL! One problem though in MS ACCESS it says "syntax error in update statement" and the cursor goes over "USING".

 

Is there any alias for this that works in MS ACCESS

 

Thanks for the support!

[Mchl]

Sorry, you'll need to find someone who knows SQL flavor used in Access ( you might check MSSQL forums)

[hiprakhar]

Thanks anyways for suggesting the query. Your help is deeply appreciated