SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    blonde.... Sarah's Avatar
    Join Date
    Jul 2001
    Location
    Berkshire, UK
    Posts
    7,442
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    changing order in table in db

    Hi,

    is it possible to change the order of the cols in the table whilst there is data in it. (mysql db)

    ie. my table is id, col_1, col_2, col_3 and I want it to be id, col_1, col_3, col_2

    I thought that I could create a new table with the cols in the correct order then copy the data across to it - but it stays in the same order! heres the code:

    insert into [newtable] (id, col_1, col_3, col_2) select * from [oldtable];

    Now I know that I want to specify which order using insert values etc but can't seem to figure the workaround.

    By the way if it sounds a really dum thing to do - I agree but my clients wants it done and asap also

    thanks

    Sarah

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alas - I have never considered doing such a dumb thing. I know you admit its dumb and that its the client that is insisting on it - but, for me, it would be one of those times where I would just throw up my hands and say "and if you asked me to sever my head with a chainsaw do you think I would?". I mean, the order of attributes (columns) in a relation (table) is completely irrelevent and its meaning as are the order in which tuples (rows) are placed into a relation.

    I suggest you say to this client that "it would be unwise to reorder attributes in the relation least it lead to some unforsean trasitive dependecy upon a non-prime attribute." Complete ******** but that should shut them up.

    The order of attributes in an index is important - sure. But an index is easy to drop and recreate. Anyway, you probably can do it and its probably easily done - but a pointless exercise

    Sorry for the rant. Best of luck.

  3. #3
    blonde.... Sarah's Avatar
    Join Date
    Jul 2001
    Location
    Berkshire, UK
    Posts
    7,442
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    continue ranting it made me laugh, I wish I could say it to him but he is one of those that learns it so much quicker than you and has already figured out to merge the two db together!?! and until I mentioned php 2 months ago he hadn't heard of it!!! I am planning to pounce on my db collegue first thing Monday am to see if she can figure it out.

    also its partly my fault with the db being ordered wrongly I just didn't think that he would want to merge them together - if I had.... well talk about shutting the gate after the bull has bolted!!

    Never mind I have made the alterations although the mind is boggling soemwhat and I am looking forward to sitting down with a drink later on, and although its probably not the best or sweetest way to go about it it works so I am pleased...

    Thanks for your thoughts greatly needed in a time of stress.

    Sarah

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK then - well here is one way you might be able to do it. there might be easier ways too -but this is off the top of my head:

    Lets say you have col_10, col_20 and col_30.
    You want to place col_30 between col_10 and col_20
    Lets say col_30 is of type INT .

    1) add the column

    ALTER TABLE TableName
    ADD col_25 INT AFTER col_10

    2) copy over the contents of the old column to the new

    UPDATE TableName
    SET col_25 = col_30


    3) Drop the old column

    ALTER TABLE TableName
    DROP col_30

    4) Change the new column name to be the same as the old

    ALTER TABLE TableName
    CHANGE col_25 col_30 INT

    PS - I hope you are backing up your databases before trying on this voodoo.
    Last edited by freakysid; Sep 14, 2001 at 11:48.

  5. #5
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sarah, your new table has the columns in the order you want, right? if so then you're on the right track in your first example. but instead of saying SELECT *, explicitly name the columns:

    insert into [newtable] (id, col_1, col_3, col_2) select id, col_1, col_3, col_2 from [oldtable];

    i think that should work.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  6. #6
    blonde.... Sarah's Avatar
    Join Date
    Jul 2001
    Location
    Berkshire, UK
    Posts
    7,442
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    cheers guys DR_LaRRY_PEpPeR that is how I managed to do it - finally! and its all done and everyone is happy.

    FreakySid - I didn't know you could do that will keep that in mind - oh and yes I have SO many backups just in case!

    Also you can seemingly insert from one db to another but not directly in mysql you need a tool that can store a resulst file and insert it?... will let you knwo when and if I find out more

    Sarah


Bookmarks

Posting Permissions

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