Databases
StackOverflow2013
Postsdbo
POFOR UPDATE cannot be specified on a READ ONLY cursor
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFOR UPDATE cannot be specified on a READ ONLY cursor
    primarykey
    data
    text
    <p>I'm using a cursor to update a single field in a table, and I'm attempting to declare the cursor using an <code>ORDER BY</code> in the text of the select.</p> <p>I've got the following example data:</p> <pre><code>testTable: RecordGuid RecordID DupeParentID ---------- -------- ------------ [guid] A Y [guid] A N [guid] A N [guid] A N [guid] B Y [guid] B N [guid] B N [guid] C Y [guid] C N [guid] C N </code></pre> <p>And script:</p> <pre><code>DECLARE @allcounter INT SET @allcounter = 1 SELECT RecordID, count(*) as [NumberDupes] INTO #RecordGroupCounts FROM testTable GROUP BY RecordID DECLARE @temp VARCHAR(500) DECLARE @current VARCHAR(500) DECLARE c1 CURSOR FOR SELECT RecordID FROM testTable WHERE RecordID IN (SELECT RecordID FROM testTable WHERE DupeParentID = 'Y') ORDER BY RecordID FOR UPDATE OF RecordID OPEN c1 FETCH NEXT FROM c1 INTO @current FETCH NEXT FROM c1 INTO @current WHILE @@fetch_status = 0 BEGIN UPDATE testTable SET RecordID = RecordID + '-' + cast(@allcounter AS VARCHAR) WHERE CURRENT OF c1 IF (@allcounter + 1) = (SELECT [NumberDupes] FROM #RecordGroupCounts WHERE RecordID = @current) BEGIN FETCH NEXT FROM c1 INTO @current SET @allcounter = 0 END SET @allcounter = @allcounter + 1 FETCH NEXT FROM c1 INTO @current END CLOSE c1 DEALLOCATE c1 </code></pre> <p>The desired output of all of this is:</p> <pre><code>RecordGuid RecordID DupeParentID ---------- -------- ------------ [guid] A Y [guid] A-1 N [guid] A-2 N [guid] A-3 N [guid] B Y [guid] B-1 N [guid] B-2 N [guid] C Y [guid] C-1 N [guid] C-2 N </code></pre> <p>I'm working with SQL Server 2000 so I don't have <code>ROW_NUMBER()</code> available - I know the common way to do this is with loops, but I am by no means a DBA, and this currently works if I remove my <code>ORDER BY RecordID</code> in the cursor declaration.</p> <p>With as small as my current test table is this seems to be working fine, but the reason I'm attempting to order this is that I'm fairly sure it'll break if the RecordIDs aren't in order (by <code>RecordID ASC, DupeParentID DESC</code>) and I intend to use this on a much larger set of records semi-regularly. Is there a way to define the order for a cursor that updates? Is the cursor ordered automatically somehow? If not, is there a simpler (or faster) way to write this for SQL Server 2000?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USIan Pugsley
    1. USIan Pugsley
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POFOR UPDATE cannot be specified on a READ ONLY cursor
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POFOR UPDATE cannot be specified on a READ ONLY cursor
      1. USWKordos
      1. VTFavorite
    3. VO
      singulars
      1. POFOR UPDATE cannot be specified on a READ ONLY cursor
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI'm not sure if I see where a lack of order by on the record_id would break this. Although it would appear the order in which the duplicates are marked as -1 -2 -3 etc... is a bit arbitrary. The faster way to write this would be with a lack of a loop and turn it into something set based, but that might be challenging. As a complete side note, your population of the c1 cursor can be redone to SELECT RecordID FROM testTable WHERE DupeParentID = 'Y' group by recordID (the group will eliminate duplicates, I suspect that was your goal with the where in).
      singulars
      1. POFOR UPDATE cannot be specified on a READ ONLY cursor
      1. USTwelfth
    2. CO@Tewlfth - currently (with the order by), I get the specified error message. Without it, after some testing, it runs, but it appears the cursor doesn't order the data at all, and so my counter updates records with an incorrect number (I get things like `A, A-1, A-2, C-3, A, C-1, C-2`...). As for the `DupeParentID = 'Y'`, I need every record that has a RecordID of `DupeParentID = 'Y'` (for the cursor itself) - not just the 'Y' records, but all of them matching a RecordID with a Y.
      singulars
      1. POFOR UPDATE cannot be specified on a READ ONLY cursor
      1. USIan Pugsley
    3. COWas hoping someone else would take a run at answering this...the mix of non-unique data and a cursor is pretty brutal. Mind if I try a different angle on this? Correct me if I'm wrong, but you currently have data in a table and this script is being used as a one time correction (IE, you don't plan on using this logic in on-going prod)...if that's the case, I do have another idea for you to handle this. Is their anything in the 'testable' to differentiate the 3 recordID A Dupeparent_id N, or do you actually have 3 identical records to number here?
      singulars
      1. POFOR UPDATE cannot be specified on a READ ONLY cursor
      1. USTwelfth
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload