Brainstorming: help on opening a cursor doing a fetch in a more effici

[SteveMe]

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

 

[Sawatzky]

Dear Steve,

am I right that you want to update allways the very row you just fetched?
If so , the FOR UPDATE part of the Declare cursor statement could be helpfull.

regards

Astrid

 

[SteveMe]

Astrid,

Your correct. I'll be reading a row and immediately updating it. For Update: as you mentioned I have commented out. I'll look into it. I'm trying to be more efficient and I can see my coding not working in causes where I have more than one record which meets my update statement. Thanks!

Steve

 

[SteveMe]

Update Coding that works

use DOH

DECLARE @a varchar(3), @b varchar(3), @c money
declare @d money, @e money, @whse varchar(3)

DECLARE myCursor CURSOR FOR SELECT WHSE, Item, Cost, Running_Total FROM _ ORDER BY WHSE, Cost desc
FOR UPDATE of Running_Total

set @whse = '~~~'

OPEN myCursor

FETCH NEXT FROM myCursor INTO @a, @b, @c, @d
WHILE @@FETCH_STATUS <> -1
BEGIN
-- This is executed as long as the previous fetch succeeds.

if @whse <> @a
begin
set @whse = @a
set @e = 0
end

set @e = @e + @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)

Update _ SET Running_Total = @e where Current of myCursor
FETCH NEXT FROM myCursor INTO @a, @b, @c, @d --, @d

END

CLOSE myCursor
DEALLOCATE myCursor
set nocount on

 

[SteveMe]

Ok one last question. If I pull the primary key off of the table I receive this error.

Server: Msg 16957, Level 16, State 4, Line 6
FOR UPDATE cannot be specified on a READ ONLY cursor.


How can I get around this what do I need in the coding above that will allow for the fetch to be able to update on a table that has a primary index and one that doesn't. Please advise. Thanks

Steve

 

[fluteplr]

All tables should have a primary index. If they don't have one, add one.

 

[SteveMe]

Fluteplr: I agree with you 100%. But in the case where there isn't a primary index. How would you get around this issue. Thanks

Steve

 

[Sawatzky]

Dear Steve,

add one )

regards astrid