Update Query Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Query Problem

Author

Topic

moz_m3
Starting Member

3 Posts

Posted - 2008-05-27 : 14:35:17

I have slight issue updating from One table to another

I am trying to update any rows which already have a unique order id and simply add the existing amount onto the new amount each table looks like the below:

The Order Table: (desired result on table)
Orderid (PK) || q || id || price ||
1 1000 A 5
3 160 C 20
4 400 B 27.5

The Fills Table:
Orderid || q || id || Price || Fillid (PK)
1 1000 A 5 1
4 200 B 25 2
4 200 B 30 3
3 10 C 20 4
3 90 C 20 5
3 60 C 20 6

So i simply want to aggregate q(by sum grouping by orderid taking the avg price)...

The code i have at present is

update Or
set Or.q = Fi.q + Or.q, Or.price = Fi.price,
from Fills Fi inner join Orders Or on Or.Orderid = Fi.Orderid

Any help would be most appreciated

Thanks

Moz

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-27 : 15:09:15

Do you need to group on ID also or jsut OrderID?

DECLARE @Orders TABLE(Orderid INT, q INT, id CHAR(1), Price MONEY)
DECLARE @Fills TABLE (Orderid INT, q INT, id CHAR(1), Price MONEY, Fillid INT PRIMARY KEY)

INSERT @Orders
SELECT 1, NULL, 'A', NULL
UNION ALL SELECT 3, NULL, 'C', NULL
UNION ALL SELECT 4, NULL, 'B', NULL


INSERT @Fills
SELECT 1, 1000, 'A', 5, 1
UNION ALL SELECT 4, 200, 'B', 25, 2
UNION ALL SELECT 4, 200, 'B', 30, 3
UNION ALL SELECT 3, 10, 'C', 20, 4
UNION ALL SELECT 3, 90, 'C', 20, 5
UNION ALL SELECT 3, 60, 'C', 20, 6


UPDATE 
	O
SET 
	q = SumQ, 
	Price = AvgPrice
FROM 
	@orders AS O
INNER JOIN
	(
		SELECT OrderID, SUM(q) as SumQ, AVG(Price) AS AvgPrice
		FROM @Fills
		GROUP BY OrderID
	) AS T
	ON T.OrderID = O.OrderID

SELECT *
FROM @Orders

moz_m3
Starting Member

3 Posts

Posted - 2008-05-27 : 15:27:21

Lamprey:
On ID and order ID

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-27 : 15:35:40

[code]UPDATE
O
SET
q = SumQ,
Price = AvgPrice
FROM
@orders AS O
INNER JOIN
(
SELECT OrderID, ID, SUM(q) as SumQ, AVG(Price) AS AvgPrice
FROM @Fills
GROUP BY OrderID, ID
) AS T
ON T.OrderID = O.OrderID
AND T.ID = O.ID[/code]

moz_m3
Starting Member

3 Posts

Posted - 2008-05-27 : 15:57:31

Thats great thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources