changing order in table in db

[Sarah]

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

[freakysid]

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.

[Sarah]

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

[freakysid]

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.

[DR_LaRRY_PEpPeR]

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.

[Sarah]

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