x

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)
more ▼

asked Jan 21, 2012 at 02:23 AM in Default

avatar image

Haren
51 10 10 11

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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 < '01/01/2012'
          GROUP BY product_code
         ) AS A
 WHERE   A.product_code = product_code
more ▼

answered Jan 21, 2012 at 03:25 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

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

Jan 21, 2012 at 03:42 AM Haren

Sorry its nice, no records satisfying this condition.

Jan 21, 2012 at 03:45 AM Haren

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.

Jan 21, 2012 at 04:17 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1208
x113
x54

asked: Jan 21, 2012 at 02:23 AM

Seen: 1112 times

Last Updated: Jan 22, 2012 at 02:03 AM

Copyright 2018 Redgate Software. Privacy Policy