Adding multiple columns AFTER a specific column in MySQL
You cannot mention multiple column names with commas using ADD COLUMN
. You need to mention ADD COLUMN
every time you define a new column.
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
If you want to add a single column after a specific field, then the following MySQL query should work:
ALTER TABLE users
ADD COLUMN count SMALLINT(6) NOT NULL
AFTER lastname
If you want to add multiple columns, then you need to use 'ADD' command each time for a column. Here is the MySQL query for this:
ALTER TABLE users
ADD COLUMN count SMALLINT(6) NOT NULL,
ADD COLUMN log VARCHAR(12) NOT NULL,
ADD COLUMN status INT(10) UNSIGNED NOT NULL
AFTER lastname
Point to note
In the second method, the last ADD COLUMN
column should actually be the first column you want to append to the table.
E.g: if you want to add count
, log
, status
in the exact order after lastname
, then the syntax would actually be:
ALTER TABLE users
ADD COLUMN log VARCHAR(12) NOT NULL AFTER lastname,
ADD COLUMN status INT(10) UNSIGNED NOT NULL AFTER lastname,
ADD COLUMN count SMALLINT(6) NOT NULL AFTER lastname
This one is correct:
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`;