SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
Thread: changing order in table in db
-
Sep 14, 2001, 08:36 #1
- 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
-
Sep 14, 2001, 08:56 #2
- 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.
-
Sep 14, 2001, 11:12 #3
- 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
-
Sep 14, 2001, 11:37 #4
- 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.
-
Sep 14, 2001, 14:14 #5
- 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
-
Sep 18, 2001, 06:08 #6
- 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