Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    15

    Unanswered: Updating horrible disorderly primary key values.

    Using phpymyAdmin I want to update every row's primary key and need an SQL statement to do it. I assume that because I'm dealing with Primary Keys that I'd need to create a temp column which when completed I can delete the first PK column and replace it with the new one?

    There are 2 columns
    PK
    Placename

    So I firstly want to order all the rows by placename in alphabetical order. All the primary keys are mucked up and horrible and so I want to make it nice and orderly!

    So I need to start by changing the first row's PK to 1. I then want to update every ID with an increment of one i.e. the PK of the next row will be 2 - all the way until the final row is updated.

    Any ideas?? I obviously don't want to do it manually if possible...fingers crossed!!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by googly
    All the primary keys are mucked up and horrible and so I want to make it nice and orderly!
    but why? what is the purpose of your PK column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    15
    Well the Primary Key is to uniquely identify each place name. The reason I say that all the primary keys are mucked up is that I have deleted about half the contents of the table and so PKs go 1, 3, 8, 12, 13, 16 etc. The table is not actually in use yet and I'd prefer to have it a 'tidy' as possible before it goes into use.
    i.e
    PK
    1
    2
    3
    4
    5
    6

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by googly
    ....I'd prefer to have it a 'tidy' as possible before it goes into use....
    why?

    a primary key rarely has any significance outside the db itself - its merelty a pointer.

    if you want a sequence then you will need to relaod the data, ensuring that all child tables have the revised key aswell. it can be done, but apart from looking orderly it acheives nothing.

    The only reasons I can see to do it are if you are worried that you will run out of autogenerated numbers.

    often I find it usefull to point out to novice userrs that the systme generated number HAS NO MEANING so they don't start applying a menaing to such numbers.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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