UPDATE product_master SET total_opening = A.Ope_qty + B.RCPT_QTY -C.ISS_QTY FROM (SELECT Product_code, Sum(Opening_stock ) as Ope_qty FROM Opening_stock Group By Product_code ) as A WHERE A.product_code = product_master.product_code LEFT JOIN (SELECT Product_code, SUM(Quantity) AS iss_qty FROM issue_detail WHERE slip_date < '30/01/2012' GROUP BY product_code ) AS C WHERE C.product_code = product_master.product_code left join (Select Product_Name,Sum(Quantity) as Rcpt_Qty from Purchase_detail Group by Product_Code ) As B WHERE B.PRODUCT_CODE = PRODUCT_MASTER.PRODUCT_CODE
Take a step back. Instead of multiple GROUP BY on each table, each in a sub-select, and all trying to stuff in an update statement... whew... Step back, and try writing the SELECT statement to get the information you need just doing standard JOIN syntax: SELECT .... FROM Opening_stock AS os LEFT JOIN issue_detail AS id ON... LEFT JOIN purchase_detail AS pd ON... In order to determine that you can get the information you want. Then, try using the GROUP BY statement on this query. It ought to work as a single statement. Then work it into your UPDATE statement. The trick when working with TSQL is to think in terms of batches. You're trying to break things down into individual statements and then join them back together. That's killing you. Combine it all up and I think you'll find it easier.