Need an advice on 'declare cursor' statement

[gene]

Hi all

I run next declare cursor statement:

declare c1 cursor
for
select date_modified, event_modified_cd, rec_id
from physicalsecurity
order by date_modified, event_modified_cd, rec_id
for update of date_modified, event_modified_cd, rec_id

.... and I am getting next error:

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

I tried different options like 'keyset' or 'scroll' - the
same simple problem.
Does anyone know what I am doing wrong?

Thank you, Gene.

[Jane]

It seems that you're missing other necessary components in
the cursor command, e.g. FETCH. Please check SQL
BookOnline.

>-----Original Message-----
>Hi all
>
>I run next declare cursor statement:
>
>declare c1 cursor
>for
>select date_modified, event_modified_cd, rec_id
>from physicalsecurity
>order by date_modified, event_modified_cd, rec_id
>for update of date_modified, event_modified_cd, rec_id
>
>.... and I am getting next error:
>
>Server: Msg 16957, Level 16, State 4, Line 3
>FOR UPDATE cannot be specified on a READ ONLY cursor.
>
>I tried different options like 'keyset' or 'scroll' - the
>same simple problem.
>Does anyone know what I am doing wrong?
>
>Thank you, Gene.
>.
>

[Mike John]

A cursor containg an ORDER BY is automatically read-only, But you can always do updates with a where clause rather than "current of cursor" to get round it.

Even better lose the cursor and do the process in one statement. Can you post the whole problem and see if someone can remove the need for the cursor?

Mike John

"gene" <gene_golub@hotmail.com> wrote in message news:03f401c3471f$fd4f0b40$a301280a@phx.gbl...

> Hi all
>
> I run next declare cursor statement:
>
> declare c1 cursor
> for
> select date_modified, event_modified_cd, rec_id
> from physicalsecurity
> order by date_modified, event_modified_cd, rec_id
> for update of date_modified, event_modified_cd, rec_id
>
> ... and I am getting next error:
>
> Server: Msg 16957, Level 16, State 4, Line 3
> FOR UPDATE cannot be specified on a READ ONLY cursor.
>
> I tried different options like 'keyset' or 'scroll' - the
> same simple problem.
> Does anyone know what I am doing wrong?
>
> Thank you, Gene.

[gene]

Thank you Mike

Yes, it's always 'get around' exists.
I had to remove duplicate rows from table and it seemed
the most elegant solution.
I will do that without using cursor.

Thank you for your tip on ordered cursors. I never knew
that.

Gene.

>-----Original Message-----
>A cursor containg an ORDER BY is automatically read-only,

But you can always do updates with a where clause rather
than "current of cursor" to get round it.

>
>Even better lose the cursor and do the process in one

statement. Can you post the whole problem and see if
someone can remove the need for the cursor?

>
>Mike John
>
>"gene" <gene_golub@hotmail.com> wrote in message

news:03f401c3471f$fd4f0b40$a301280a@phx.gbl...

>> Hi all
>>
>> I run next declare cursor statement:
>>
>> declare c1 cursor
>> for
>> select date_modified, event_modified_cd, rec_id
>> from physicalsecurity
>> order by date_modified, event_modified_cd, rec_id
>> for update of date_modified, event_modified_cd, rec_id
>>
>> ... and I am getting next error:
>>
>> Server: Msg 16957, Level 16, State 4, Line 3
>> FOR UPDATE cannot be specified on a READ ONLY cursor.
>>
>> I tried different options like 'keyset' or 'scroll' -

the

>> same simple problem.
>> Does anyone know what I am doing wrong?
>>
>> Thank you, Gene.

>
>.
>

[gene]

Thank you Jane for your respond

Other fetch statements were there but I did not even get
to them in a code.
The problem was that 'order by' in a declare cursor makes
this cursor automaticaly 'read only'.
That's were my problem lied.

Thanks again, Gene.

>-----Original Message-----
>It seems that you're missing other necessary components

in

>the cursor command, e.g. FETCH. Please check SQL
>BookOnline.
>

>>-----Original Message-----
>>Hi all
>>
>>I run next declare cursor statement:
>>
>>declare c1 cursor
>>for
>>select date_modified, event_modified_cd, rec_id
>>from physicalsecurity
>>order by date_modified, event_modified_cd, rec_id
>>for update of date_modified, event_modified_cd, rec_id
>>
>>.... and I am getting next error:
>>
>>Server: Msg 16957, Level 16, State 4, Line 3
>>FOR UPDATE cannot be specified on a READ ONLY cursor.
>>
>>I tried different options like 'keyset' or 'scroll' - the
>>same simple problem.
>>Does anyone know what I am doing wrong?
>>
>>Thank you, Gene.
>>.
>>

>.
>