如何在一个ALTER表中添加和删除列

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?

是否可以在同一个ALTER TABLE语句中执行一个DROP和一个ADD ?

2 个解决方案

#1

If you look at the ALTER TABLE SYTAX

如果您查看可更改的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 }

修改表{ALTER COLUMN column_name | ADD | DROP}

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

根据Transact-SQL语法约定(Transact-SQL) |(垂直条)

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

如果您需要它们作为原子操作，您只需在事务中进行包装。

#2

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

如果你的数据库是Mysql，你可以这样做

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

Works in mysql 5.5.5

在mysql 5.5.5工作

#1