record counter (key counter) in t-sql using cursor

I have this function in access and need to move it to sql  

>> Dim d As Database
>> Dim r As Recordset
>> Dim x As Integer
>> Dim CurrKey As String, PrevKey As String
>> 
>> Set d = CurrentDb
>> 
>> Set r = d.OpenRecordset("select autonum_key, autonum_count from table1 order by autonum_key")
>> x = 1
>> Do Until r.EOF = True
>>     CurrKey = r("autonum_key") 'set the current key value
>>     If CurrKey <> PrevKey And r.AbsolutePosition <> 0 Then 'if they're the same, reset the counter
>>         x = 1
>>     End If
>>     r.Edit
>>         r("autonum_count") = x
>>     r.Update
>>     x = x + 1
>>     PrevKey = CurrKey 'save the current key for comparison
>>     r.MoveNext
>> Loop
>> r.Close
>> 

would it look like this ?

SET NOCOUNT ON
PRINT 'POPULATE CHARGE KEY COUNTER'
PRINT GETDATE()
DECLARE @X       INT
DECLARE @CurrKey VARCHAR(10)
DECLARE @PrevKey VARCHAR(10)
SET     @CurrKey = ''
SET     @PrevKey = ''
SET     @X       = 1
DECLARE CK CURSOR
READ_ONLY
FOR SELECT CHARGE_KEY FROM CHPAYADJCRDB  ORDER BY CHARGE_KEY
OPEN CK
FETCH NEXT FROM CK INTO @CurrKey
WHILE (@@fetch_status <> -1)
  BEGIN
    IF (@@fetch_status <> -2)
      BEGIN
        IF @CurrKey <> @PrevKey
          SET @X = 1
        ELSE
          SET @X = @X + 1
        UPDATE CHPAYADJCRDB
        SET CHARGE_KEY_COUNTER = @X WHERE CHARGE_KEY = @CurrKey
        SET @PrevKey = @CurrKey
        END
FETCH NEXT FROM CK INTO @CurrKey
    END
CLOSE CK
DEALLOCATE CK
GO

the result would look like this:

key         count
12345        1
12345        2
12345        3
12346        1
12346        2
12347        1
12348        1
12349        1
12349        2
123410      1

and so on ...

any help would be appreciated
thbrowneAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
rdrunnerConnect With a Mentor Commented:
Ok try 3.... Sorry... Its past midnight here :)

update table1 outer
Set autonum_count = (Select count (*) +1 from table1 inner where inner.autonum_key = outer.autonumkey  and autonum_count is null)
where chargekey IN (select min(chargekey) from Table1 group by autonum_key having autonum_count is null)

This should work...
0
 
rdrunnerCommented:
How about this?


select autonum_key,count(*) autonum_count from table1 group by autonum_key



Its a lot shorter to type ;)
0
 
rdrunnerCommented:
OOOPS....

I think i missunderstood a bit....

You want to update the Autonum key field in all rows to a correct Value?


Ok i assume that chargekey is a "real id" that constantly grows...

update table1 outer
Set autonum_count = (Select count (*) +1 from table1 inner where inner.autonum_key = outer.autonumkey  and autonum_count is null)
where chargekey = (select min(chargekey) from Table1 inner2 where inner2.autonum_key = outer.autonum_key and autonum_count is null)


I think this should work... You need to run this querry several times until no rows are affected..

Hope this helps
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.