Trying to avoid using a cursor....

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

Date: Sat, 11 Sep 2004 22:25:02 -0700

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

  • RE: 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 ... > Thanks for any assistance, ...
    (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)