RE: Trying to avoid using a cursor....

From: TomT (tomt_at_tomt.com)
Date: 09/15/04 

Date: Wed, 15 Sep 2004 15:09:04 -0700

Thanks to all who assisted, this is working perfectly after implementing your
suggestions.

"TomT" wrote:

> I'm trying to update a column in a table, and as recommended by so many, do
> it without resorting to a cursor.
>
> The situation is more complex than what I am presenting here, but if someone
> can show me a way to get the simplified version working I can go from there.
>
> E.g., I have a table with 10 rows, and a column 'test'. The first record has
> a value of 1 for this column, remaining rows have NULL in the column. I want
> to run an procedure that will take the value of that first record in column
> test, and place values in that column for each of the next 9 records,
> incremented by 1. I.e. row 2 would have 2 in the column, row 3, would have 3,
> and so on.
>
> I've tried using the following, but each row gets a value of 2 in the 'test'
> column:
>
> update mytable set test = t.maxtest + 1
> from
> (select max(test) as maxtest
> from mytable where test is not null) as t
> where test is null
>
> Coming from a VB background, my first impulse was to just use a cursor and
> move through the records one by one, but I've read often in this forum that
> most anything that can be done with a cursor can (and usually should) be done
> without resorting to that method.
>
> The above is as I mentioned a very simplied version of what I need to do, a
> set based solution to this will give me what I need to know for the actual
> task I need to accomplish.
>
> Thanks for any assistance,
>
> Tom Tucker

Relevant Pages

  • Trying to avoid using a cursor....
    ... it without resorting to a cursor. ... to run an procedure that will take the value of that first record in column ... move through the records one by one, but I've read often in this forum that ...
    (microsoft.public.sqlserver.programming)
  • Re: SP Question
    ... less overhead than a cursor. ... > Hi, Hari ... >> First record you can retrive using TOP clause in select statement. ... You need to use cursor with FETCH LAST. ...
    (microsoft.public.sqlserver.server)
  • Re: select mline returns truncated values
    ... > SELECT MLINEFROM zoot WHERE SelectMe ... Look at the structure of your cursor. ... This is what VFP does - it evaluates the first record in the ... > the first record is not included in the output set, ...
    (microsoft.public.fox.programmer.exchange)
  • select mline returns truncated values
    ... CREATE CURSOR zoot ... SELECT MLINEFROM zoot WHERE SelectMe ... The filter on SelectMe proves this, as the first record is not included ...
    (microsoft.public.fox.programmer.exchange)
  • Re: Search and Replace gives GPF error
    ... I can only get it to work if the cursor is on the first record of parent ... Both as TableFrames with 5-9 rows showing. ... operation Customer is only showing one row. ...
    (comp.databases.paradox)