question

Haren avatar image
Haren asked

Sql query aliasing problem

Hello all, I'm running a sql query to update one table from getting sum of quantity from another table. I have Product_master & Issue_detail table, I want to update total_opening field below is my query which gives error ( incorrect syntax near as in line 1 ) use dpexcise go update product_master set total_opening =0 go update product_master as P LEFT OUTER JOIN ( SELECT Product_code, sum(Quantity) as iss_qty from issue_detail where slip_date < '01/01/2012' group by product_code ) as A ON a.Product_code = p.product_code set p.total_opening = IIF(iss_qty IS NULL,0,iss_qty)
sqlhomeworksub-query
10 |1200

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

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
Your structure for the UPDATE query is a little off. You wan it to look like this: UPDATE product_master SET total_opening = A.iss_qty FROM (SELECT Product_code, SUM(Quantity) AS iss_qty FROM issue_detail WHERE slip_date
3 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.

Once again thanks Fritchey, I tried the above code with little change ( last line a.product_code = Product_Master.Product_Code ), it execute without en error but 0 rows affected. Please help what is going ? Thanks
0 Likes 0 ·
Sorry its nice, no records satisfying this condition.
0 Likes 0 ·
Great. Glad to hear it. The best way to test updates like this is to first test the select statement. By the way, if you do find answers helpful, please use the voting buttons to give them a thumbs up. If an answer is the one that solved your problem, also click on the check box. That way other people will know what you thought the answer was.
0 Likes 0 ·

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.