Post reply

Update table without cursors and Temp tables , in a single query

  • January 9, 2009 at 2:50 am

    #198113

    HI ,

    I have a table with id column null and there no primary key and uniqueys avialable in table i cannot identify a row uniquely

    Create table (id int, names varchar(50)

    there are 100 rows in the table, I want to update the table (id from 1 to 100) without using cursors and temptables, in a single query

    Please suggest any me any methods

    Regards

    Rao

  • January 9, 2009 at 2:53 am

    #923997

    Create table (id int, names varchar(50))

    you can update id column like below.

    declare @id int

    set @id=0

    update table

    set id=@id,@id=@id+1

  • January 9, 2009 at 3:20 am

    #924005

    thank you very much barunkmallick

  • January 9, 2009 at 12:43 pm

    #924502

    That's absolutely the way to do it... but, I'd be tempted to just drop the ID column and add it back in as an IDENTITY column. When you add an IDENTITY column, it will number all of the rows. It has the side benefit of being self maintaining for any new rows you may wish to add.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply