CODEWITHSUNDEEP
mysqlsqldatabase
Jason12
Jason12

Reputation: 369

Adding multiple columns in MySQL with one statement

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?

Upvotes: 29

Views: 60467

Answers (7)

ravi teja
ravi teja

Reputation: 1

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

this is also working fine

Upvotes: 0

Abhishek
Abhishek

Reputation: 39

This will help you:

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

Upvotes: 1

MontyPython
MontyPython

Reputation: 2994

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

Upvotes: 0

stealth
stealth

Reputation: 359

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

Upvotes: 7

Damian Jauregui
Damian Jauregui

Reputation: 1

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

Upvotes: 0

ashkufaraz
ashkufaraz

Reputation: 5297

 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

Upvotes: 60

Darren
Darren

Reputation: 70728

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;

Upvotes: 14

Related Questions