question

Haren avatar image
Haren asked

how do i solve ?

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
stored-procedureshomework
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

A question for you first. What are you trying to solve?
0 Likes 0 ·
I think it is related to the question [Multiple queries][1]. Is it so? [1]: http://ask.sqlservercentral.com/questions/84236/multiple-queries
0 Likes 0 ·

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.