Adding multiple columns AFTER a specific column in MySQL

Question

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?

What dialect are you speaking? Looks like mysql to me. But the `ALTER TABLE` syntax varies a bit between dialects. — Damien_The_Unbeliever, Jul 09 '13 at 06:25
possible duplicate of [MySQL Alter Table Add Field Before or After a field already present](http://stackoverflow.com/questions/3379454/mysql-alter-table-add-field-before-or-after-a-field-already-present) — David Jashi, Jul 09 '13 at 06:27
I think you need an AFTER keyword on each of the columns you're adding. Whether that means you need all 3 to be AFTER lastname or a separate ALTER TABLE statement per new column, I can't say for sure. — Zec, Jul 09 '13 at 06:31

score 920 · Accepted Answer · answered Jul 09 '13 at 06:32

920

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

answered Jul 09 '13 at 06:32

Ayyappan Sekar

11,007
2
18
22

17

[_As an additional information_] Multiple `ADD`, `ALTER`, `DROP`, and `CHANGE` clauses are permitted in a single `ALTER TABLE` statement, separated by commas. This is a **MySQL extension** to standard SQL, which permits only one of each clause per ALTER TABLE statement. – informatik01 May 01 '19 at 13:21
@Ayyappan Can we do this in sql server? – Roshan Apr 04 '20 at 08:13
what is the equivalent in bigquery ? – Oussama Bamaàrouf Nov 10 '21 at 19:02
nice. i can suggest that if you don't want to specify a different last column each time that you use the original column after caluse but place the columns in the reverse order in the query so they appear in the order you desire – Sarfaraaz Jun 02 '22 at 16:32

score 94 · Answer 2 · edited Jun 20 '20 at 09:12

94

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 18 '13 at 10:34

user3106476

941
6
2

5

The second version worked perfectly well for me. I'm using MySql 5.5.25. – Norman Apr 06 '14 at 09:06
does the last bit of code add the columns in the order `count`, `log`, `status` or `count`, `status`, `log`? – Sarfaraaz Jul 20 '15 at 09:06
1

note: if you don't add the 'AFTER lastname' bit, then the ordering in which the last column you put in that alter table list comes first doesn't really apply. So when you do ALTER TABLE table ADD COLUMN blah1, ADD COLUMN blah2; (note no 'AFTER' clause), the column blah1 will be appended first, then blah2. – Hongyi Li Jan 20 '16 at 01:06
what is the equivalent in bigquery ? – Oussama Bamaàrouf Nov 10 '21 at 19:03
The second method doesn't work. It results in `count` and `log` being added as the last columns. However, in the "Point to note", the syntax there *does* work: you need an `AFTER` clause for each `ADD COLUMN`. – Jake Feb 19 '23 at 02:40

score 11 · Answer 3 · answered May 13 '15 at 10:10

11

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`;

answered May 13 '15 at 10:10

Denys Popov

1,040
12
14

1

This code is equivalent to the [top answer](https://stackoverflow.com/a/17541485/711006). Please [edit your answer](https://stackoverflow.com/posts/30211729/edit) to describe what and why is different in your case, otherwise it’s superfluous. – Melebius Apr 25 '22 at 11:41

score 11 · Answer 4 · edited Dec 15 '15 at 23:08

11

You cannot mention multiple column names with commas using ADD COLUMN. You need to mention ADD COLUMN every time you define a new column.

edited Dec 15 '15 at 23:08

ChrisGuest

3,398
4
32
53

answered Dec 15 '15 at 22:29

Piyush Saxena

127
1
5

5

I gave a +1 but this should be a comment to the accepted answer – mjsarfatti May 07 '17 at 16:09
You can do `ALTER TABLE 'tablename' ADD ('field1' TEXT, 'field2' TEXT)`, just (apparently) not with an `AFTER` clause. – Jake Feb 19 '23 at 02:12

score 10 · Answer 5 · answered Mar 07 '20 at 20:19

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.

score 3 · Answer 6 · edited Aug 23 '19 at 13:11

3

ALTER TABLE `users` ADD COLUMN
`COLUMN NAME` DATATYPE(SIZE) AFTER `EXISTING COLUMN NAME`;

You can do it with this, working fine for me.

edited Aug 23 '19 at 13:11

user3783243

5,368
5
22
41

answered Sep 08 '14 at 07:22

Gaurav Singh

189
2
7

OP wants to add multiple columns using `AFTER` with a single SQL query, not just one. – Jake Feb 19 '23 at 02:14

score 1 · Answer 7 · answered Jul 09 '13 at 06:30

1

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.

answered Jul 09 '13 at 06:30

Ahmed

452
3
7

4

if i may add my two cents, "placement" whether table columns or rows, should be immaterial. Spreadsheets use location to find things, databases do not. – Mike S. Oct 01 '13 at 19:57

score 0 · Answer 8 · answered Nov 15 '19 at 07:11

0

ALTER TABLE listing ADD count INT(5), ADD log VARCHAR(200), ADD status VARCHAR(20) AFTER stat

It will give good results.

answered Nov 15 '19 at 07:11

Solomon Suraj

1,162
8
8

This does not work. Only `status` is added immediately after `stat`. The other columns are added at the end. – Jake Feb 19 '23 at 02:21

Mahmoud Hesham · Answer 9 · 2021-02-06T18:38:56.100

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;

score -1 · Answer 10 · edited May 16 '17 at 09:52

-1

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';

edited May 16 '17 at 09:52

Michael Dodd

10,102
12
51
64

answered May 16 '17 at 08:36

Aiswarya T S

15
3

2

Stealing a comment to get votes. Don't do that mate. – MBouwman Apr 13 '19 at 12:36

score -1 · Answer 11 · answered Jul 19 '19 at 01:34

-1

The solution that worked for me with default value 0 is the following

ALTER TABLE reservations ADD COLUMN isGuest BIT DEFAULT 0

answered Jul 19 '19 at 01:34

Jorge Santos Neill

1,635
13
6

OP wants to add *multiple* columns after a specific column. Your answer does not add multiple columns, and does not add the column immediately after another. – Jake Feb 19 '23 at 02:55

Adding multiple columns AFTER a specific column in MySQL

11 Answers11

Point to note

Linked

Related