as the subject implies... I've got a DB with data already in it... is it possible to change the order of the fields?
is there a way to reorder the fields in a MySQL db?
- Thread starter jacksonpt
- Start date
Tables are in databases, fields are in tables...
So you want to change field order in a table?
May I ask why?
So you want to change field order in a table?
May I ask why?
Yes, I'm aware of that. My point was that I already have data in the DB, so I can't just drop the table and recreate it (or the field and re-insert it). (EDIT: sorry if that sounded snotty).johnbryanpeters said:
I have to export the data from MySQL into access, save it as a txt file, then use that text file to import the data into another DB. I'll have to do this on a regular basis, so the less I have to tweak the data before importing it, the better.
It's safer to import the data into Access, then select what you want in the order you want before exporting.
You can restructure tables - Create table B, select table A into table B, drop table A, select B into A, drop B. Danger is screwing up relations.
You can restructure tables - Create table B, select table A into table B, drop table A, select B into A, drop B. Danger is screwing up relations.
Why wouldn't you just export in the order you prefer? It's not like it's any more work to export in order D,A,C,B than A,B,C,D...
Am I not understanding what you want?
Am I not understanding what you want?
I'm with BV here, do you need the field IDs to change as well, or just the order that it is listed in the txt file?
Perhaps, but again, for as frequently as I'm going to be doing this, I'd rather have it setup right from the get go, then not have to worry about it down the road. It will also be easier to have people do the export from MySQL when I am out of town.johnbryanpeters said:
That's an idea...johnbryanpeters said:
I export from MySQL to Access via an ODBC connection (File | Get External Data). Access imports the table from MySQL as is... I'm not aware of any way to tweak that import into Access. Once the data is in Access, I save it as a text file.
The text files is fixed length - each field having a specific character length. The import utility expects certain fields to begin and end at certain points in the file. Thus, the text file has to be just so. The import doesn't care about field IDs, as long as "last name" starts at position 15, and so on.
I am pretty sure you could create an access macro that would reorder things for you, in short order
hrm... I'll check into that, thanks.Transcend said:
Create a query in Access and export the query. Don't screw around with the table.jacksonpt said:
SELECT
<field name>, <field name>, ..., <field name>
FROM
<table name>
You can do the whole thing drag-and-drop, order the fields however you want.
Someday when you have time, take a course in Access or buy a book.
ok, I create a duplicate table to play with and tried this out:
I need to move a field called "Title" down in the order. Would something like this work (not sure if the code is dead on, but you get the point)?
It worked.
Now, I'm all self taught with this stuff, so I'm sure I have some bad habits. While this functionally seems to have worked, is there a reason why it's a bad idea to do this? It's been suggested a couple of times that I not touch the table - why?
I need to move a field called "Title" down in the order. Would something like this work (not sure if the code is dead on, but you get the point)?
Code:
alter table test add column temp varchar(10);
update test set temp = Title;
alter table test drop column Title;
alter table test add column Title varchar (10) after LastName;
update test set Title = temp;
alter table test drop column temp;Now, I'm all self taught with this stuff, so I'm sure I have some bad habits. While this functionally seems to have worked, is there a reason why it's a bad idea to do this? It's been suggested a couple of times that I not touch the table - why?
I'm not heavily into database stuff, but I've had a lot of Oracle background.jacksonpt said:
There are two reasons I wouldn't touch the table, and both come from cliches:
A) If it ain't broke... The table is fine. There's nothing wrong with it. Why would you move however many tens or hundreds of thousands of pieces of data around just so that you don't have to do the extremely simple re-order in Access?
B) Keep it simple, stupid. Ditto to above. You can set up a permanent macro in Access that will be a two or three click export. Why risk corrupting the table for this simple export routine?
I'm not exactly sure how the SQL references work, but in complex Excel worksheets (tens of thousands of lines of data and many calculation sheets), I hesitate many times to move big chunks of data around if there's an easy workaround because you then have to go through the portion of the spreadsheet that manipulates or calls up the data and make sure all of your references are still correct.
If you record a macro in Access of your import and export, you can just run the macro every time you need to do it. It's literally two or three clicks - and it's no slower to run the macro if you're exporting in default order or a specific order.