Adding multiple columns in MySQL with one statement

Question

I am trying to add multiple columns to an existing table in phpMyAdmin, but I keep getting the same error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax ...

I am writing:

ALTER TABLE `WeatherCenter`
   ADD COLUMN
      BarometricPressure SMALLINT NOT NULL,
      CloudType VARCHAR(70) NOT NULL,
      WhenLikelyToRain VARCHAR(30) NOT NULL;

I have referred to past posts on StackOverflow, and I am following the experts' recommendation, so why am I getting an error?

Possible duplicate of [alter table add MULTIPLE columns AFTER column1](https://stackoverflow.com/questions/17541312/alter-table-add-multiple-columns-after-column1) — zuluk, May 24 '17 at 07:23

score 59 · Answer 1 · answered Nov 28 '14 at 21:19

 ALTER TABLE table_name
 ADD COLUMN column_name datatype

correct syntax

ALTER TABLE `WeatherCenter`
   ADD COLUMN BarometricPressure SMALLINT NOT NULL,
   ADD COLUMN CloudType VARCHAR(70) NOT NULL,
   ADD COLUMN  WhenLikelyToRain VARCHAR(30) NOT NULL;

check syntax

score 13 · Answer 2 · answered Nov 28 '14 at 21:17

13

You need to specify multiple ADD COLUMN

ALTER TABLE `WeatherCenter`
      ADD COLUMN  BarometricPressure SMALLINT NOT NULL,
      ADD COLUMN CloudType VARCHAR(70) NOT NULL,
      ADD COLUMN WhenLikelyToRain VARCHAR(30) NOT NULL;

answered Nov 28 '14 at 21:17

Darren

68,902
24
138
144

stealth · Answer 3 · 2017-12-27T03:38:54.003

6

You can alter a table and add multiple columns in one statement by doing it like this.

alter table WeatherCenter add column (BarometricPressure SMALLINT NOT NULL, CloudType VARCHAR(70) NOT NULL, WhenLikelyToRain VARCHAR(30) NOT NULL);

edited Dec 27 '17 at 03:38

answered Dec 27 '17 at 03:22

stealth

319
6
5

score 1 · Answer 4 · edited Mar 08 '19 at 07:14

1

This will help you:

alter table A add first_name varchar(10),last_name varchar(10);

edited Mar 08 '19 at 07:14

Pang

9,564
146
81
122

answered Dec 25 '18 at 13:05

Abhishek

39
1

score 0 · Answer 5 · edited Oct 12 '16 at 09:17

0

As you're adding columns to an existing table I don't think you're meant to declare NOT NULL in the statement. Also, you don't need to use ADD COLUMN, you can just use ADD.

ALTER TABLE WeatherCentre
   ADD BarometricPressure SMALLINT,
   ADD CloudType VARCHAR(70),
   ADD WhenLikelyToRain VARCHAR(30);

edited Oct 12 '16 at 09:17

Mohit Kumar Arora

2,204
2
21
29

answered Nov 24 '15 at 13:41

Damian Jauregui

1

score 0 · Answer 6 · edited Mar 26 '18 at 19:21

0

This is from Official MySQL Documentation

ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

alter_specification:
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)

Possible duplicate of alter table add MULTIPLE columns AFTER column1

edited Mar 26 '18 at 19:21

ADTC

8,999
5
68
93

answered Oct 12 '16 at 09:27

MontyPython

2,906
11
37
58

ravi teja · Answer 7 · 2019-03-11T11:51:18.160

0

alter table table_name add (product varchar(20) not null, price int(10))

this is also working fine

edited Mar 11 '19 at 11:51

answered Mar 08 '19 at 05:57

ravi teja

1
2

Adding multiple columns in MySQL with one statement

7 Answers7

Linked