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

User · Answer

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

User · Answer

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   lt br gt        for  i 0    i lt count  fields     i             if  i  0                output    ADD COLUMN    fields  i    VARCHAR 15  AFTER    startF         lt br gt              else               output    ADD COLUMN    fields  i    VARCHAR 15  AFTER    fields  i-1          lt br gt                         extra fields without the array      output    ADD COLUMN col four VARCHAR 255  AFTER any col u want        lt br gt         output    ADD COLUMN col five VARCHAR 255  AFTER col four        lt br gt         output    ADD COLUMN col six VARCHAR 255  AFTER col five     lt br gt           echo  output

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

ALTER TABLE listing ADD count INT 5   ADD log VARCHAR 200   ADD status VARCHAR 20  AFTER stat   It will give good results

User · Answer

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

User · Answer

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

User · Answer

ALTER TABLE  users  ADD COLUMN  COLUMN NAME  DATATYPE SIZE  AFTER  EXISTING COLUMN NAME     You can do it with this  working fine for me

User · Answer

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

User · Answer

The solution that worked for me with default value 0 is the following  ALTER TABLE reservations ADD COLUMN isGuest BIT DEFAULT 0

[mysql] Adding multiple columns AFTER a specific column in MySQL

Point to note

Examples related to mysql

Examples related to ddl