mySQL: Move Column

I created a table in a mySQL database.

Now I want to move the column `uid` to the VERY END so it is the last column.
CREATE TABLE IF NOT EXISTS `audit` (
  `id` int(11) NOT NULL auto_increment,
  `uid` int(11) NOT NULL,
  `userName` varchar(255) NOT NULL,
  `action` varchar(255) NOT NULL,
  `entryDate` int(11) NOT NULL,
  `orgId` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

Open in new window

LVL 16
hankknightAsked:
Who is Participating?
 
Roger BaklundConnect With a Mentor Commented:
The table name was wrong in my statement, your table is named "audit", not "user". This should work:
alter table audit change uid uid int not null after orgId;

Open in new window

0
 
mostartCommented:
This will work but it will delete any records inside..

alter table audit drop uid;
alter table audit add uid int(11) not null AFTER orgId;

0
 
mostartCommented:
if you want to do and keep existing records you need to copy table before:

#create temporary table
create table audit_tmp LIKE audit;

# copy records
insert into audit_tmp (select * from audit);

# empty original table
truncate table audit;

# move the column
alter table audit drop uid;
alter table audit add uid int(11) not null AFTER orgId;

# copy content back
insert into audit (id,userName,action,entryDate,orgId,uid) values (select id,userName,action,entryDate,orgId,uid from audit_tmp) ;


# drop tmp table
drop table audit_tmp
0
 
Roger BaklundCommented:
You can move the column with a single ALTER TABLE statement:
alter  table user change uid uid int not null after orgId;

Open in new window

0
 
mostartConnect With a Mentor Commented:
cxr is perfectly right. I just did not know you could add the AFTER keyword to the CHANGE as well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.