locked
Update the Primary Key for Every Row Using Concantination RRS feed

  • Question

  • I have a table which is very important to the overall database for which I need to change every rows primary key value.

    The the primary key value of every row currently is a seven digit number: 0000000

    What I need to do is insert a '.' after the first five characters so the example above will become: 00000.00

    This field is an NvarChar(12)

    I think concatenation is the answer but I'm a little fuzzy on the T-SQL syntax.

    I suppose I could write a Visual Basic.net program but

    I would like to see how it's done in T-SQL.

    OH, My DB server is SQL SERVER 2000.

     

    Thanks for any and all help with this.

    Ric

    Tuesday, August 2, 2011 7:09 PM

Answers

  • I think this will be risky, what if the value already exist in your table???

    Update myTable

    Set myPK = SubString(myPK, 1, 5) + '.' + SubString(myPK, 6, 6)


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Proposed as answer by Surendra Nath GM Tuesday, August 2, 2011 7:25 PM
    • Marked as answer by Chigrboy Tuesday, August 2, 2011 9:33 PM
    Tuesday, August 2, 2011 7:12 PM
  • Use this - Please change tablename and columname accordingly

    Update <TableName> set <ColumnName> = Left(ColumnName,5) +'.'+Right(columnName,2)


    http://uk.linkedin.com/in/ramjaddu
    • Marked as answer by Chigrboy Tuesday, August 2, 2011 9:33 PM
    Tuesday, August 2, 2011 7:12 PM

All replies

  • I think this will be risky, what if the value already exist in your table???

    Update myTable

    Set myPK = SubString(myPK, 1, 5) + '.' + SubString(myPK, 6, 6)


    Best Wishes, Arbi --- MCC 2011; Please vote if you find this posting was helpful or Mark it as answered.
    • Proposed as answer by Surendra Nath GM Tuesday, August 2, 2011 7:25 PM
    • Marked as answer by Chigrboy Tuesday, August 2, 2011 9:33 PM
    Tuesday, August 2, 2011 7:12 PM
  • Use this - Please change tablename and columname accordingly

    Update <TableName> set <ColumnName> = Left(ColumnName,5) +'.'+Right(columnName,2)


    http://uk.linkedin.com/in/ramjaddu
    • Marked as answer by Chigrboy Tuesday, August 2, 2011 9:33 PM
    Tuesday, August 2, 2011 7:12 PM
  • WOW! You guys answered so quickly! Thank you both and how simple is that! Fantastic!

    I, of course, won't try this on the production DB and will vet it out in a test environment before giving it a try in production.

     

    Arbi, good point about being risky but I'm positive that there currently no decimals in the this field and injecting one into the current values

    will not create a duplicate. But again, I will make certain of this in a test environment.

     

    Both replies are very similar in their solutions and I'll mark accordingly after testing them out.

     

    Thanks guys!

     

     

     

    Tuesday, August 2, 2011 7:22 PM
  • Another way to do it is with the STUFF function

    UPDATE YourTable SET YourKey = STUFF(YourKey, 6, 0, '.');

    Tom

    • Proposed as answer by Badii Gharbi Tuesday, August 2, 2011 7:45 PM
    Tuesday, August 2, 2011 7:40 PM
  • Tom - I didn't try the STUFF function but this is a new function to me and love the concept of it!

    Thank you for your reply!

     

    Tuesday, August 2, 2011 9:35 PM