April 26, 2006 at 12:36 pm
I am fairly green when it comes to T-SQL but I cannot seem to wrap my mind around how to perform a simple update query
Within our corporate CRM, I have two tables - OrdMain and OrdItem. All I want to do is periodically update the order total in OrdMain from the sum of the orders related product prices in OrdItem. We hava nasty bug within the system that sometime causes the grand total for the order to be incorrect. The bug fix is coming but I would like to at least have a backup routine in place to ensure the order totals are correct.
I know this is a simple update - but I am having a helluva time. Here's what I have hacked together thus far:
UPDATE OrdMain
SET
ListTotal = SumOfListItem
FROM OrdMain INNER JOIN
(SELECT SUM(ListEach) AS SumOfListItem
FROM OrdItem
GROUP BY OrderNo) AS A ON A.Order_ID = OrdMain.Order_ID
WHERE (OrdMain.Order_Num = N'11589');
I know it looks strange but I need that WHERE clause at the end to reference one single order for now just for testing.
If anyone can point me in the right direction - I would appreciate it!
Cheers.
Bruce
April 26, 2006 at 12:43 pm
Try something more along this route:
UPDATE
OrdMain
SET
ListTotal
= A.SumOfListItem
FROM
(
SELECT OrderNo, SUM(ListEach) AS SumOfListItem
FROM OrdItem
GROUP BY OrderNo) AS A
WHERE
OrdMain.Order_ID = A.OrderNo
AND
OrdMain.Order_Num = N'11589'
If I had the field mappings from table to table, I could be more accurate, as I am sure the Order_ID doesn't map to OrderNo, but the idea is to create the items you want to update along with the matching values within the subquery, then match the match fields in the where clause, updateing the sum in the update line....
April 26, 2006 at 12:57 pm
Scorpion,
Thank you very much for the assistance. Sorry for the crappy code there...here is some corrections and cleanup...but it still gives errors:
When I run this in Query Analyzer
UPDATE OrdMain
SET
ListTotal = A.SumOfListItem
FROM
(SELECT SUM(ListEach) AS SumOfListItem
FROM OrdItem)
AS A
WHERE OrdMain.Order_ID = A.Order_ID
AND OrdMain.Order_Num = N'11589'
I get..
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Order_ID'.
Thoughts?
April 26, 2006 at 1:02 pm
Scorp,
I think I got it!
UPDATE OrdMain
SET
ListTotal = A.SumOfListItem
FROM
(SELECT Order_ID,SUM(ListEach) AS SumOfListItem
FROM OrdItem
GROUP BY Order_ID)
AS A
WHERE OrdMain.Order_ID = A.Order_ID
AND OrdMain.Order_Num = N'11589'
I was missing the Group By and the item updated correctly. Gonna try it on a few more now and see how it goes.
Any ideas on getting this into a stored procedure or something I can schedule within SQL Enterprise Manager?
Thanks again!
Bruce
April 26, 2006 at 1:07 pm
Sure...just put a wrapper on it....
Create Proc usp_proc_name As
UPDATE OrdMain
SET
ListTotal = A.SumOfListItem
FROM
(SELECT Order_ID,SUM(ListEach) AS SumOfListItem
FROM OrdItem
GROUP BY Order_ID)
AS A
WHERE OrdMain.Order_ID = A.Order_ID
AND OrdMain.Order_Num = N'11589'
and take the where for order_num 11589 off....
April 26, 2006 at 1:26 pm
Awesome. Thanks again!
Cheers!
B
April 26, 2006 at 7:27 pm
Everythin is much simpler:
UPDATE OrdMain
SET
ListTotal = SUM(ListEach)
FROM OrdItem
WHERE OrdItem.Order_ID = OrdMain.Order_ID
AND (OrdMain.Order_Num = N'11589');
_____________
Code for TallyGenerator
April 27, 2006 at 3:27 am
CREATE PROCEDURE USP_PROC_XXXX
(
@v_Order_No VARCHAR(5)
)
AS
BEGIN
set nocount on
set Transaction Isolation Level Serializable
UPDATE OrdMain
SET
ListTotal = A.SumOfListItem
FROM
(SELECT Order_ID,SUM(ListEach) AS SumOfListItem
FROM OrdItem
GROUP BY Order_ID)
AS A
WHERE OrdMain.Order_ID = A.Order_ID
-- AND OrdMain.Order_Num = N'11589'
AND OrdMain.Order_Num = @v_Order_No
END
April 27, 2006 at 8:03 am
Sergiy:
This is not correct due to the aggregate function...here's what the server says with your code:
Server: Msg 157, Level 15, State 1, Line 3
An aggregate may not appear in the set list of an UPDATE statement
You must use the subquery to get this to work.
April 27, 2006 at 2:18 pm
Yes, of course,
but idea is you don't need to mention the same table twice:
UPDATE OrdMain
SET
ListTotal = SUM_Each
FROM (select OrderId , SUM(ListEach) SUM_Each
from OrdItem
group by OrderId ) DT
WHERE DT.Order_ID = OrdMain.Order_ID
AND (OrdMain.Order_Num = N'11589')
_____________
Code for TallyGenerator
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply