September 30, 2003 at 12:04 pm
Good job ghughes. You don't need to include the master table the second time, but you did fix a problem with J Moseley's earlier update statement by including the OrderID and Type again in the subquery.
Guarddata-
September 30, 2003 at 12:06 pm
My guess is that the temp table solution would be the fastest but I'm not sure. Bet we have some experts out there that would know for sure! What say you, gurus?
J. Moseley
[font="Courier New"]ZenDada[/font]
September 30, 2003 at 12:10 pm
What do you mean I didn't need to include the master? I dont understand.
September 30, 2003 at 12:12 pm
it's reference is already in the update statement, don't put it in the from clause.
J. Moseley
[font="Courier New"]ZenDada[/font]
September 30, 2003 at 12:13 pm
I have 182 dts jobs that run in a 2.5 hour time frame. some of the tables have 100s of thousands of records. I avoid using temp tables whenever I can. BUT you are right, the temp table solution is faster (about 5 seconds)
Edited by - ghughes on 09/30/2003 12:13:45 PM
October 1, 2003 at 4:44 am
Sub queries can give nice concise code, but are not very efficient. With a big data set you are much better off creating an intermediate temp table - not so elegant but much faster. The other thing with sub queries & performance is to return as few records as possible from the sub query. Anyway - here is some code, using a subquery, that works.
P
update tbl_Master
set om_note = o.txt_notes
from tbl_OrderNotes o inner join tbl_Master m on m.OrderID = o.OrderID
where o.NOTEID =
(
select max(NOTEID)
from tbl_OrderNotes p
where o.ORDERID = p.ORDERID
group by OrderID
)
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply