July 22, 2005 at 1:12 am
hi
please help wid this query
update [copy_orders]
set sumorders=(select sum(quantity) from [order details] od
where od.orderid=[copy_orders].orderid
group by od.orderid )
above is a corelated subquery that updates the copy_orders table with sum of quantity in order details table where the orderId matches, can it be converted to a simple join qery ?
thanks
July 22, 2005 at 2:51 am
Without having tested anything, I guess that the following query gives the same result:
update [copy_orders]
set sumorders=(select sum(quantity) from [order details] od
where od.orderid=[copy_orders].orderid)
That's a simplification but still not a join
July 22, 2005 at 2:55 am
I think that your requirement for row aggregation rules out a simple join query directly. However, why not create a view of
select orderID, sum(quantity) from [order details] group by orderID
and then you can build your simple join using this view.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 22, 2005 at 3:09 am
Or you could try this:
update c
set c.sumorders = t.sum
from copy_orders c inner join
(select orderid, sum(quantity) as sum from [order details] group by orderid) t
on c.orderid = t.orderid
July 22, 2005 at 3:21 am
i like the idea , but isnt it same as the corelated subqueries ??
we cannot use the views as we are not dealing directly with the data and jus makin a generic function for it.
Amrita
July 22, 2005 at 3:25 am
This is my understanding of a join query
Could you be more specific as to how you have access to data?
July 22, 2005 at 3:27 am
This is not strictly a corelated subquery - the subquery runs independently of the outer query, as the subquery's results do not depend on values from the outer query. I can't see how you're going to get any better.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 22, 2005 at 4:32 am
Thanks, Phil, for this explanation - I didn't know what a corelated subquery was. I should also mention that my query is nothing but your view idea - except that I use a virtual table (I hope that's the correct word ) instead of a view.
July 22, 2005 at 4:43 am
Hi Jesper
I was actually answering the previous message; not yours, which made perfect sense. Your solution effectively replaces my view idea with a standard (non-corelated ?) subquery and is the best solution I can think of in this case.
Regards, Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 29, 2005 at 12:40 am
thanks for the query , this one seems to work nicely
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply