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?

Answer 1

 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

Answer 2

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;

Answer 3

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