How to move a Field? 1

[Germaris]

Hi there!

I inserted a new field in my MySQL table and populated it by hand.
Now, I find it to be in the wrong place i.e. I want to move it from the end of the table to the center of the table just after a determined field.

How do I perform this task?

Many thanks in advance for your help!

 

[towerbase]

Use ALTER TABLE MODIFY COLUMN

For example, if your column was called 'fred' and contained integers then the command to move it after the column called 'tom' would be

ALTER TABLE MODIFY COLUMN fred INT AFTER tom

Try the on-line documentation for the full syntax.

http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html

Andrew
Hampshire, UK

 

[LittleSmudge]

Why ? WHY ? WHY ?

The location of the field in the field order doesn't affect anything material - so why bother ?





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Germaris]

Thanks for the tip and the link!

I want to move the field only for comfort of use!
Say in PHPMyAdmin half of my table (which has sixteen Fields) is out of the screen and it's very boring to scroll horizontally to view the misplaced Field which is located at the end of the table.

Cheers!

 

[LittleSmudge]

Andrew,

Does that alter Table instruction preserve the contents of that field ?

I was concerned that it deleted the existing field ( and all it's contents ) and then inserts a new ( empty ) field in the new location.

???

( Okay Germaris - fair cop. A 'real' reason. )

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Germaris]

The following query worked perfectly and preserved the entire content of the column:
ALTER TABLE listaix MODIFY COLUMN region INT AFTER country

I confess I am a beginner and not very familiar with the MySQL Syntax.
In that moved column there are unnecessary '0' in many rows. I want to delete those 'zeros'. Which will be the right syntax?

I have also another more complex question to ask but I'll post it later. One step at a time!

Best Regards.

 

[towerbase]

Germaris, you should start a new thread for a new topic.

LittleSmudge, if you followed the link I posted earlier you would have read this

When you change a column type using CHANGE or MODIFY, MySQL tries to convert existing column values to the new type as well as possible.

Andrew
Hampshire, UK

 

[LittleSmudge]

The unwanted zeros could be as a result of having the default set to zero in that field

To set the default to NULL you can use another Alter Table command

ALTER TABLE listaix ALTER region SET DEFAULT Null ;


You can then convert the zeros to Nulls using

UPDATE listaix SET region = Null WHERE region = 0 ;



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[duncdude]

LittleSmudge

The fields can make a huge difference... If a Perl script had been written to insert records without being specific of the order this could muck things up badly? I would think this would be poor scripting but i'm sure this goes on!?


Kind Regards
Duncan

 

[Germaris]

Okay TowerBase, I'll start a new Thread. Seems logical...
LittleSmudge, your syntax was no less than PERFECT !!!
Many thanks to both of you.
See my new thread in a few minutes...

Best Regards.

Contact: gm[at]germaris.com
My motto: "Simple is Best