Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    165

    Unanswered: renumber a column...

    Hi. How can I renumber a column in my database? This is what we use to identify a particular record:
    Code:
    CASEID    PARTYID
    11        1
    11        2
    23        1
    23        2
    23        3
    I want to stop renumering for each entry. How can I update the entire so each PARTYID is renumbered. I want to make this the primary key for the table. Thanks for your help!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    CREATE TABLE myTable99(CASEID int, PARTYID int)
    GO
    
    INSERT myTable99(CASEID, PARTYID)
    SELECT 11,        1 UNION ALL
    SELECT 11,        2 UNION ALL
    SELECT 23,        1 UNION ALL
    SELECT 23,        2 UNION ALL
    SELECT 23,        3
    GO
    
    
    SELECT * FROM myTable99
    GO
    
    ALTER TABLE myTable99 ADD newPARTYID int IDENTITY(1,1) PRIMARY KEY
    GO
    
    SELECT * FROM myTable99
    GO
    
    EXEC sp_rename 'myTable99.PARTYID', 'PARTYID_Old', 'COLUMN'
    EXEC sp_rename 'myTable99.newPARTYID', 'PARTYID', 'COLUMN'
    GO
    
    SELECT * FROM myTable99
    GO
    
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2005
    Posts
    165
    Thanks! Worked like a charm!

Posting Permissions

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