[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 557
  • Last Modified:

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

0
hankknight
Asked:
hankknight
  • 3
  • 2
2 Solutions
 
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
 
mostartCommented:
cxr is perfectly right. I just did not know you could add the AFTER keyword to the CHANGE as well.
0
 
Roger BaklundCommented:
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now