Hi all,
I am trying to sequence rows in a table.  this is for differnt policy#'s
I am using policy#,effectiveDate as my critiria to start my numbering.  The
problem is there are no unique Identifiers in this table I have 6 rows that
are exact dupes.  when I run a normal cursor, it updates all 6 with the same
seqNo( I understand why).
I can't get the staement where current of cursorName to work.  I get an
error saying:
"Msg 16929, Level 16, State 1, Line 35
The cursor is READ ONLY.
The statement has been terminated."

I need to be able to update ONLY the row in the cursor...

I am including my code below..................Yes TransTime can also be a
dupe.

Any help would be greatly appreciated.
Joe
/******************************/
declare @seqid int, @PolicyNo nvarchar(10), @EffDate datetime,
@TransactionDate datetime, @TranactionTime float,
@KeyHold nvarchar(255),@Key nvarchar(255)

select @seqid=0
Select @keyHold = ' '
Select @key = ' '

declare SEQID cursor for
select policyNo,EffDate,Transactiondate,TranactionTime
from tblpolicyTest
order by policyNo,EffDate,Transactiondate,TranactionTime

open  seqid

fetch next from seqid into
@PolicyNo,@EffDate,@TransactionDate,@TranactionTime

while @@fetch_status=0
   begin

Select @key = rtrim(isnull(@PolicyNo,' '))+rtrim(isnull(@EffDate,' '))

if @key <> @keyHold
begin
  select @seqid =1
    update tblpolicyTest
    set SeqNo = @seqid
where current of seqid
--    Where PolicyNo = @PolicyNo
--    And EffDate = @EffDate
--    And TransactionDate = @TransactionDate
--    And TranactionTime = @TranactionTime
end
else
begin
    select @seqid =@seqid+1
    update tblpolicyTest
    set SeqNo = @seqid
where current of seqid
--      Where PolicyNo = @PolicyNo
--    And EffDate = @EffDate
--    And TransactionDate = @TransactionDate
--    And TranactionTime = @TranactionTime
end

Select @keyHold = rtrim(isnull(@PolicyNo,' '))+rtrim(isnull(@EffDate,' '))

    fetch next from seqid into
    @PolicyNo,@EffDate,@TransactionDate,@TranactionTime

   end

close seqid
deallocate Seqid
/***************************************/

Dups are bad, If you have a maintance window you could add a column
with an identity value (moving the table a new one), sort out the dups,
delete them (one of the dups remaining for each occurence), then
dropping the identity. But anyway, did you try the UPDATE extensions in
the cursor ?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp


HTH, jens Suessmeyer.

Thanks Jen,
But my dupes are really not dupes but transactions that may reverse others
out.  these are coming from an AS400 system, and the way they update policies
are to insert a reversing records then add a new record.  so there can be
dupelicate records but in reality they are not dupes.
Yes I tried to use the Update extension, but I get an error saying I can't
use the update on a read only cursor.

Thanks again,
Joe

Show quoteHide quote

SQL Server needs a PK for finding the row for updating. So try to use
the approach to define a PK on the table. If you are doing so think
about using a set based solution rather than the cursor approach.

HTH, Jens Suessmeyer.

Hi Jen,
Thanks for the answers...  I have added a uniqueID field so I am now able to
update the correct row.  the problem is Cursors are soooooooooo slow and I
have over 5,000,000 rows in this table I need to update.
I have been trying to locate some samples of a set based solution, but I
have been unsuccessful. 
Can you point me to any articles?  I searched the knowledgebase but came up
empty.
Thanks,
Joe

Show quoteHide quote

jaylou wrote:
jaylou wrote:
Why? If the rows really were duplicates then they are redundant. Adding
a uniqueid doesn't make them any less redundant.

INSERT INTO new_table (col1,col2,col3)
SELECT col1, col2, col3
FROM your_table
GROUP BY  col1, col2, col3 ;

If you need to preserve the number of transactions:

INSERT INTO new_table (col1,col2,col3, tran_count)
SELECT col1, col2, col3, COUNT(*) AS tran_count
FROM your_table
GROUP BY  col1, col2, col3 ;

jaylou wrote:
This doesn't make any sort of sense to me. If transactions reverse each
other out then presumably they can't be duplicates because their
amounts would be the inverse of each other or some other column would
indicate the fact of the reversal. This contradicts your previous
assertion that the duplicates are exact. Please post proper DDL and
sample data including keys and constraints so that we don't have to
guess this stuff.

If you have duplicates you should remove them first. You can use SELECT
GROUP BY to do that. No need for a cursor.

David,
The Dupes that are exact are situations where there is a payment of $100 and
the CSR enters $50 by mistake.  they have to now add another record of $50
which would be a dupe because all fields including trans date are =.  this is
an AS400 system where they can not delete or update any reocrds, so they have
to add entries for deletes and updates.

Thanks for the sample.  I am sure this will be be just what I need.
Thanks,
Joe
Show quoteHide quote

If you want an updateable cursor, then declare it as such.  Please refer to
BOL for the appropriate syntax.

Really really slow cursor
Problem copying data from column in same table
reverse like?
Can't we use variables in OPENQUERY, FREETEXT("@searchstring")?
inserting single precision data into sql server float column