update query with subquery question, New to SP's?

[DirectOne]

I am using the following to updata a single order, but need to update a batch of orders (all orders with a specific item).

How can I run this for many orders?

ALTER PROCEDURE don_allocation_tot
(@order char(8))

AS UPDATE OEORDHDR_SQL 
SET  
OEORDHDR_SQL.TOT_SLS_AMT = (SELECT Sum(OEORDLIN_SQL.QTY_TO_SHIP*OEORDLIN_SQL.UNIT_PRICE) AS ext_sale 
FROM OEORDLIN_SQL 
WHERE  oeordlin_sql.ord_no = @order),

OEORDHDR_SQL.TOT_COST = (SELECT Sum(OEORDLIN_SQL.QTY_TO_SHIP*OEORDLIN_SQL.UNIT_COST) AS ext_cost 
FROM OEORDLIN_SQL 
WHERE  oeordlin_sql.ord_no = @order),

OEORDHDR_SQL.TOT_WEIGHT =(SELECT Sum(OEORDLIN_SQL.QTY_TO_SHIP*OEORDLIN_SQL.UNIT_WEIGHT) AS ext_wgt 
FROM OEORDLIN_SQL 
WHERE  oeordlin_sql.ord_no = @order)

WHERE (oeordhdr_sql.ord_no = @order) and oeordhdr_sql.ord_type = 'O'

 

[rsshetty]

One way is to use cursors.

declare @order int -- change it to varchar if you have to

declare orderlist cursor for
select orderid from your_order_table

open orderlist
fetch next from orderlist into @order

while @@fetchstatus = 0
begin

--***********your update statement for each @order
UPDATE OEORDHDR_SQL
SET
OEORDHDR_SQL.TOT_SLS_AMT = (SELECT Sum(OEORDLIN_SQL.QTY_TO_SHIP*OEORDLIN_SQL.UNIT_PRICE) AS ext_sale
FROM OEORDLIN_SQL
WHERE oeordlin_sql.ord_no = @order),

OEORDHDR_SQL.TOT_COST = (SELECT Sum(OEORDLIN_SQL.QTY_TO_SHIP*OEORDLIN_SQL.UNIT_COST) AS ext_cost
FROM OEORDLIN_SQL
WHERE oeordlin_sql.ord_no = @order),

OEORDHDR_SQL.TOT_WEIGHT =(SELECT Sum(OEORDLIN_SQL.QTY_TO_SHIP*OEORDLIN_SQL.UNIT_WEIGHT) AS ext_wgt
FROM OEORDLIN_SQL
WHERE oeordlin_sql.ord_no = @order)

WHERE (oeordhdr_sql.ord_no = @order) and oeordhdr_sql.ord_type = 'O'

--*******************************
fetch next from orderlist into @order
end

rsshetty.
It's always in the details.

 

[Pattycake245]

How are these ord_id's coming across? Through a web app or from a table inside your query? At this point you are only passing 1 order id. Either way, this can be accomplished without the use of a cursor. If the order_id's come from a table, you can simply join on that table and update all at once. If it is a comma separated list from a web app you can separate these order_id's into a temp table or table variable and join on that.

Here is a cleaner version of your current update:

ALTER PROCEDURE don_allocation_tot
(@order char(8))

AS 

UPDATE A --OEORDHDR_SQL 
SET  A.TOT_SLS_AMT = B.EXT_SALE,
     A.TOT_COST = B.EXT_COST,
     A.TOT_WEIGHT = B.EXT_WGT
FROM OEORDHDR_SQL A JOIN (SELECT ORD_NO, Sum(QTY_TO_SHIP*UNIT_PRICE) AS ext_sale,
			  Sum(QTY_TO_SHIP*UNIT_COST) AS ext_cost,
			  Sum(QTY_TO_SHIP*UNIT_WEIGHT) AS ext_wgt
			  FROM OEORDLIN_SQL 
			  WHERE ORD_NO=@order AND ORD_TYPE='O')B
ON A.ORD_NO=B.ORD_NO

Tim

 

[DirectOne]

The orders are comming from a subset of the existing tables.

All orders that have item "widget" on them.

I am changing the line sale, cost and weight amount and need to update the header to reflect change in the detail records.

I have a select all and a clear all button on a form that changes the qty to ship qty on all orders for a specific item.

Thanks for all your help.

 

[DirectOne]

I took both your ideas and came up with the following.

I am not sure it will work, I am trying to figure out how to test it right now.

Is there a way to monitor the records update?

number of records?
What records changed?

ALTER PROCEDURE don_allocation_tot_all
(@order char(8), @date int, @fetchstatus int, @item char(15) )
AS
--declare @order int -- change it to varchar if you have to

declare orderlist cursor for 

SELECT     dbo.OEORDLIN_SQL.item_no, dbo.OEORDHDR_SQL.ord_no, dbo.OEORDHDR_SQL.status, dbo.OEORDHDR_SQL.shipping_dt
FROM         dbo.OEORDHDR_SQL INNER JOIN
                      dbo.OEORDLIN_SQL ON dbo.OEORDHDR_SQL.ord_no = dbo.OEORDLIN_SQL.ord_no
WHERE     (dbo.OEORDLIN_SQL.item_no = @item) AND (dbo.OEORDHDR_SQL.status IN ('1', 'C')) AND (dbo.OEORDHDR_SQL.shipping_dt <= @date)

open orderlist 

fetch next from orderlist into @order 

while @fetchstatus = 0

begin

UPDATE A
SET A.TOT_SLS_AMT = B.EXT_SALE,
A.TOT_COST = B.EXT_COST,
A.TOT_WEIGHT = B.EXT_WGT
FROM OEORDHDR_SQL A JOIN (SELECT ORD_NO, Sum(QTY_TO_SHIP*UNIT_PRICE) AS ext_sale,
				Sum(QTY_TO_SHIP*UNIT_COST) AS ext_cost,
				Sum(QTY_TO_SHIP*UNIT_WEIGHT) AS ext_wgt
				FROM OEORDLIN_SQL 
				WHERE ORD_NO=@order AND ORD_TYPE='O'
				Group by ord_no)B
ON A.ORD_NO=B.ORD_NO

fetch next from orderlist into @order 

end

 

[Pattycake245]

This will not work for a couple of reasons. Firstly, your query to create the list of orders to update doesn't need all the extra fields. This also means that this statement:

fetch next from orderlist into @order

will not work because your are querying four fields in your cursor creation and only putting one into a variable.

You can do the updating without using a cursor at all. You can put your criteria for selecting particular order id's inside your update. Something like below:

UPDATE A
SET A.TOT_SLS_AMT = B.EXT_SALE,
A.TOT_COST = B.EXT_COST,
A.TOT_WEIGHT = B.EXT_WGT
FROM OEORDHDR_SQL A 
JOIN 
(SELECT ORD_NO, Sum(QTY_TO_SHIP*UNIT_PRICE) AS ext_sale,
                Sum(QTY_TO_SHIP*UNIT_COST) AS ext_cost,
                Sum(QTY_TO_SHIP*UNIT_WEIGHT) AS ext_wgt
 FROM OEORDLIN_SQL
 WHERE item_no= @item
 AND status IN('1','C')
 AND shipping_dt <= @date
 GROUP BY ord_no)B
ON A.ORD_NO=B.ORD_NO

If you want to see which records have been updated you'll have to add a flag field or if a "last updated" field exists already, you can use that to see which records were updated.

Are you actually passing in all those variables to the proc?

ALTER PROCEDURE don_allocation_tot_all
(@order char(8), @date int, @fetchstatus int, @item char(15) )
AS...

Tim

 

[DirectOne]

The way it is writeen it will only update the header record for the @item.

I need the header (tot_sls_amt) to be a sum of all items on that order qty_to_ship*Ext_price.

But I only want to update orders which have the current item on them.

I believe that I need to pass all order numbers that have the @item on them and then run the stored procedure as you had rewritten it.

Thanks,

Bob Zaback