Subquery?

Last Post 05 Nov 2007 10:51 AM by Bsherman2. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sgfubt321
New Member
New Member

--
05 Nov 2007 08:23 AM
I have data in the following format:

f1 f2 f3
apple 648 950
orange 107 587
orange 108 587
orange 109 587
grape 245 83
grape 246 83
grape 247 83
grape 248 83
plum 167 113

I need to be able to replace the f2 column with incremental data based ont he f1 column so it looks like this:

f1 f2 f3
apple 1 950
orange 1 587
orange 2 587
orange 3 587
grape 1 83
grape 2 83
grape 3 83
grape 4 83
plum 1 113

Does anyone have an example? Thank you for your time.
Bsherman2
New Member
New Member

--
05 Nov 2007 10:51 AM
declare @Break varchar(10),
@line int,
@hold varchar(10)
set @line = 0

declare UpdateFile cursor for
select top 100 percent
f1
from test with (index = ix_test) -- Put an index on f1 column


for update of F2

open UpdateFile

nextRecord:

fetch next From UpdateFile into
@Hold
While @@fetch_Status = 0
begin

If @Break is null Set @Break = @Hold

if @Break <> @Hold
begin
set @Line = 0
Set @Break = @Hold
end

Set @Line = @Line + 1
Update Test
set
f2 = @Line

where current of UpdateFile

goto NextRecord
end

Close UpdateFile
Deallocate Updatefile

Hope this helps
sgfubt321
New Member
New Member

--
06 Nov 2007 02:56 AM
This worked perfectly. Thanks you so very much!
You are not authorized to post a reply.

Acceptable Use Policy