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  General SQL Server Forums  New to SQL Server Programming  Update Table Query

Author  Topic  AndrewA Starting Member 8 Posts Posted - 2010-07-14 : 14:59:43 I want to make this more stored procedure code more efficient. It does work, but it takes 5-6 seconds to run and I am sure it can be done quicker? This was posted at the end of a previous post but someone suggested re-posting under a new title as it is an UPDATE query not a DELETE query as per my original post. Hopefully someone has some suggestions. Regards, Andrew. --Insert relevant rows into temporary table select id, WeekUID, WeeklyTotal into #tempAccounts from tblAccounts where LastEntryOfWeek = 1 declare @MaxID int declare @Total numeric(10,2) declare @Counter int declare @ID int declare @UID varchar(15) --Loop through the temporary table one row at a time, calculating the weekly total figures --then update the temp table accordingly set @Counter = 1 select @MaxID = max(ID) from #tempAccounts while @Counter <= @MaxID begin select @ID = (select ID from #tempAccounts (nolock) where ID = @Counter) select @UID = (select WeekUID from #tempAccounts (nolock) where ID = @Counter) select @Total = (select SUM(Amount) from tblAccounts where WeekUID = @UID) update #tempAccounts set WeeklyTotal = @Total where ID = @Counter set @Counter = @Counter + 1; end --Update the live table with the data from the temp table update tblAccounts set WeeklyTotal = #tempAccounts.WeeklyTotal from #tempAccounts where tblAccounts.ID = #tempAccounts.ID --Dispose of the temp table drop table #tempAccounts This is what I have so far. It updates the right entries but inserts the total for the entire database rather than the sub total for the block of records that share a unique week id (WeekUID): UPDATE f SET f.WeeklyTotal = (select SUM(Amount) from tblAccounts where WeekUID = WeekUID) FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY [weekUID] ORDER BY [datePaid] DESC) AS [rowPos], WeeklyTotal, WeekUID, Amount FROM tblAccounts ) AS f WHERE [rowPos] = 1