November 29th, 2013, 09:10 AM
I want to update table Products with respect to the another table Products_Purchased. If I add new item to Products_Purchased, it must be entered in table Products too and also should update TotalQuantity field of table Products . Whats wrong in this query as it gives null to totalQuantity field ?and doesn't update table if new entry is entered in Products_Puchased table.
update products as a,products_purchased as b set a.total_quantity=
when a.date_of_lastupdate < b.PurchaseDate
then (select * from (select sum(products.total_quantity)+sum(products_purchased.quantity) from products as a natural join products_purchased as b)as c)
where a.productname=b.productname ;
November 29th, 2013, 02:43 PM
your subquery, the one with the sums, obtains the grand total of all purchases in both tables -- surely that's not what you want to update each row with