The following syntax will work perfectly in the situation that I have now. However, if I have the same exact cost under the same wharehouse more than once I will have issues due to my where statement in my update clause. This table doesn't have a primary index nor a unique identifier. If I have to I can create it. However, I'm trying to avoid that.
Is their a more efficient way to update a record set? Any help would be greatly appreciated. Thanks!
Steve
use DOH
DECLARE @a varchar(3), @b varchar(3), @c money
declare @d money, @whse varchar(3)
DECLARE myCursor CURSOR FOR SELECT WHSE, Item, Cost FROM _ ORDER BY WHSE, Cost desc
--FOR UPDATE
set @whse = '~~~'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @a, @b, @c
WHILE @@FETCH_STATUS <> -1
BEGIN
-- This is executed as long as the previous fetch succeeds.
if @whse <> @a
begin
set @whse = @a
set @d = 0
end
set @d = @d + @c
-- print @a + ' * ' + @b + ' * ' + cast(@c as varchar(100)) + ' * ' + cast(@d as varchar(100))
UPDATE _ SET Running_Total = @d WHERE (WHSE = @a) AND (Item = @b)
FETCH NEXT FROM myCursor INTO @a, @b, @c --, @d
END
CLOSE myCursor
DEALLOCATE myCursor
set nocount on
Is their a more efficient way to update a record set? Any help would be greatly appreciated. Thanks!
Steve
use DOH
DECLARE @a varchar(3), @b varchar(3), @c money
declare @d money, @whse varchar(3)
DECLARE myCursor CURSOR FOR SELECT WHSE, Item, Cost FROM _ ORDER BY WHSE, Cost desc
--FOR UPDATE
set @whse = '~~~'
OPEN myCursor
FETCH NEXT FROM myCursor INTO @a, @b, @c
WHILE @@FETCH_STATUS <> -1
BEGIN
-- This is executed as long as the previous fetch succeeds.
if @whse <> @a
begin
set @whse = @a
set @d = 0
end
set @d = @d + @c
-- print @a + ' * ' + @b + ' * ' + cast(@c as varchar(100)) + ' * ' + cast(@d as varchar(100))
UPDATE _ SET Running_Total = @d WHERE (WHSE = @a) AND (Item = @b)
FETCH NEXT FROM myCursor INTO @a, @b, @c --, @d
END
CLOSE myCursor
DEALLOCATE myCursor
set nocount on