Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2008
    Posts
    11

    Unanswered: UPDATE with ORDER BY?

    I have a table that holds records that will be printed, one record indicates one letter to be printed. I want to assign a printSequence number to the DB records for reference later on.

    I have acomplished this with out a temp table by using a cursor. I was wanting to know if there was a pure SQL way of doing this (A simple UPDATE perhaps?)


    declare @printSeq int;
    set @printSeq = 0;

    declare printSeqCursor cursor
    for
    Select id from tableA
    order by id; -- ORDER BY CLAUSE CAN BE COMPLEX

    open printSeqCursor

    declare @ID bigint;
    FETCH next FROM printSeqCursor INTO @ID;

    WHILE ( @@FETCH_STATUS <> -1 )
    BEGIN
    SET @printSeq = @printSeq +1;
    UPDATE tableA
    SET printSequence = @printSeq
    WHERE id = @ID;

    FETCH next FROM printSeqCursor INTO @ID;
    END
    CLOSE printSeqCursor
    DEALLOCATE printSeqCursor

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm confused as why you'd want to do this...
    But here's a reasonable solution
    Code:
    CREATE TABLE ##print_order (
       print_seq int identity(1,1)
     , id int)
    
    INSERT INTO ##print_order (id)
    SELECT id
    FROM   tableA
    ORDER
        BY id --Or whatever you want
    
    SELECT print_seq
         , id
    FROM   ##print_order
    ORDER
        BY print_seq
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2008
    Posts
    11
    When we print our letters I'm needing a top line to be the sequence number, for human readable reasons.

    essentially a printed address block would read

    1
    John Doe
    123 Street

    2
    Jane Doe
    333 Road


    I want the number in the DB so we can go back and reference past jobs. Also reprints of documents will need to refer to this number so it must exist as a data item in the DB, not just a number created at print time.


    Since both methods work, which is "preferred"? What I'm asking is which method with scale the best? or what is each method constrained by?

    I would image the cursor is CPU intensive, while the temp table might be disk/memory intensive? Am I correct?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Check ROW_NUMBER() in BOL. It gives several examples on how to generate sequential number for a block of records. This way you won't have to have a dedicated field for that.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •