How do you ADD and DROP columns in a single ALTER TABLE

Question

I tried the following but I got a syntax error

ALTER TABLE Grades ( 
DROP COLUMN (Student_FamilyName, Student_Name),
ADD Student_id INT );

Is it possible to perform a DROP and an ADD in the same ALTER TABLE statement?

You can't do drop and create with one alter statement, separate it — Vishwanath Dalvi, Apr 13 '13 at 15:39
You switch to PostgreSQL (cough), which supports [multiple changes](http://www.postgresql.org/docs/currrent/static/sql-altertable.html) as an extension to standard SQL syntax. (But even though I use PostgreSQL a lot, I *usually* write SQL that avoids extensions.) — Mike Sherrill 'Cat Recall', Apr 13 '13 at 16:31

score 29 · Answer 1 · answered Apr 13 '13 at 16:20

If you look at the ALTER TABLE SYTAX

you'll see this

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name 
{ 
    ALTER COLUMN column_name 
    { 
        [ type_schema_name. ] type_name [ ( { precision [ , scale ] 
            | max | xml_schema_collection } ) ] 
        [ COLLATE collation_name ] 
        [ NULL | NOT NULL ] [ SPARSE ]

    | {ADD | DROP } 
        { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE }
    } 
        | [ WITH { CHECK | NOCHECK } ]

    | ADD 
    { 
        <column_definition>
      | <computed_column_definition>
      | <table_constraint> 
      | <column_set_definition> 
    } [ ,...n ]

    | DROP 
     {
         [ CONSTRAINT ] 
         { 
              constraint_name 
              [ WITH 
               ( <drop_clustered_constraint_option> [ ,...n ] ) 
              ] 
          } [ ,...n ]
          | COLUMN 
          {
              column_name 
          } [ ,...n ]
     } [ ,...n ]

This can be reduced to

ALTER TABLE { ALTER COLUMN column_name | ADD | DROP }

According to Transact-SQL Syntax Conventions (Transact-SQL) the | (vertical bar)

Separates syntax items enclosed in brackets or braces. You can use only one of the items.

So you can't Alter, Drop or Add in a single statement. You also have the parens and comma that won't work. So you'll need

ALTER TABLE Grades DROP COLUMN (Student_FamilyName, Student_Name);
ALTER TABLE Grades ADD  Student_id INT;

If you need them to be an atomic action you just need to wrap in transaction

"_If you need them to be an atomic action you just need to wrap in transaction_" - Doesn't Oracle DDL cause implicit commits? — Sepster, Jul 08 '14 at 07:10
Or as in T-SQL: ALTER TABLE Grades DROP COLUMN Student_FamilyName, COLUMN Student_Name; — Zach Johnson, Jul 16 '15 at 18:51

score 8 · Answer 2 · edited Feb 14 '20 at 14:13

8

In case your database is MySQL, you can do it this way:

ALTER TABLE Grades
    DROP COLUMN Student_FamilyName, 
    DROP COLUMN Student_Name,
    ADD Student_id INT;

Works in MySQL 5.5.5

edited Feb 14 '20 at 14:13

informatik01

16,038
10
74
104

answered Nov 21 '16 at 15:02

Klesun

12,280
5
59
52

score 0 · Answer 3 · answered Dec 08 '22 at 05:19

you can use this one:

ALTER TABLE Grades
  DROP Student_FamilyName, 
  DROP Student_Name,
  ADD Student_id INT;

and you can use before/after like this:

ALTER TABLE Grades
  DROP Student_FamilyName, 
  DROP Student_Name,
  ADD Student_id INT AFTER id;

How do you ADD and DROP columns in a single ALTER TABLE

3 Answers3