Adding multiple columns AFTER a specific column in MySQL
MysqlDdlAlter TableMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
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
Solution 2 - Mysql
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
Solution 3 - 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.
Solution 4 - Mysql
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`;
Solution 5 - Mysql
ALTER TABLE `users` ADD COLUMN
`COLUMN NAME` DATATYPE(SIZE) AFTER `EXISTING COLUMN NAME`;
You can do it with this, working fine for me.
Solution 6 - Mysql
Alternatively:
ALTER TABLE users
ADD COLUMN `status` INT(10) UNSIGNED NOT NULL AFTER `lastname`,
ADD COLUMN `log` VARCHAR(12) NOT NULL AFTER `lastname`,
ADD COLUMN `count` SMALLINT(6) NOT NULL AFTER `lastname`;
Will put them in the order you want while streamlining the AFTER statement.
Solution 7 - Mysql
One possibility would be to not bother about reordering the columns in the table and simply modify it by add the columns. Then, create a view which has the columns in the order you want -- assuming that the order is truly important. The view can be easily changed to reflect any ordering that you want. Since I can't imagine that the order would be important for programmatic applications, the view should suffice for those manual queries where it might be important.
Solution 8 - Mysql
This works fine for me:
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';
Solution 9 - Mysql
> ALTER TABLE listing
ADD count
INT(5), ADD log
VARCHAR(200), ADD status
VARCHAR(20) AFTER stat
It will give good results.
Solution 10 - Mysql
I have done this code in case anyone faced my problem of adding lots of fields fast using MySQl code hope it helps , u can run this code on any online php compiler as well if u are too busy!
$fields = array(
'col_one' ,
'col_two' ,
'col_three'
);
$startF = 'after_col';
$table = 'table_name';
$output = 'ALTER TABLE ' .$table.'<br>';
for($i=0 ; $i<count($fields) ; $i++){
if($i==0){
$output.= 'ADD COLUMN '.$fields[$i].' VARCHAR(15) AFTER '.$startF.',' . '<br>';
}else{
$output.= 'ADD COLUMN '.$fields[$i].' VARCHAR(15) AFTER '.$fields[$i-1].',' . '<br>';
}
}
// extra fields without the array
$output.= 'ADD COLUMN col_four VARCHAR(255) AFTER any_col_u_want, '. '<br>';
$output.= 'ADD COLUMN col_five VARCHAR(255) AFTER col_four, '. '<br>';
$output.= 'ADD COLUMN col_six VARCHAR(255) AFTER col_five'. '<br>';
echo $output;
Solution 11 - Mysql
The solution that worked for me with default value 0 is the following
ALTER TABLE reservations ADD COLUMN isGuest BIT DEFAULT 0