Adding multiple columns AFTER a specific column in MySQL

Koala picture Koala · Jul 9, 2013 · Viewed 570.4k times · Source

I need to add multiple columns to a table but position the columns after a column called lastname.

I have tried this:

ALTER TABLE `users` ADD COLUMN
(
    `count` smallint(6) NOT NULL,
    `log` varchar(12) NOT NULL,
    `status` int(10) unsigned NOT NULL
) 
AFTER `lastname`;

I get this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AFTER lastname' at line 7


How can I use AFTER in a query like this?

Answer

Ayyappan Sekar picture Ayyappan Sekar · Jul 9, 2013

Try this

ALTER TABLE users
ADD COLUMN `count` SMALLINT(6) NOT NULL AFTER `lastname`,
ADD COLUMN `log` VARCHAR(12) NOT NULL AFTER `count`,
ADD COLUMN `status` INT(10) UNSIGNED NOT NULL AFTER `log`;

check the syntax