如何使用一个ALTER TABLE语句删除多个列?

I would like to write a single SQL command to drop multiple columns from a single table in one ALTER TABLE statement.

我想编写一个SQL命令，在ALTER table语句中从一个表中删除多个列。

From MSDN's ALTER TABLE documentation...

从MSDN的可修改文档…

DROP { [CONSTRAINT] constraint_name | COLUMN column_name }

Specifies that constraint_name or column_name is removed from the table. DROP COLUMN is not allowed if the compatibility level is 65 or earlier. Multiple columns and constraints can be listed.

指定从表中删除constraint_name或column_name。如果兼容性级别是65或更早，则不允许删除列。可以列出多个列和约束。

It says that mutliple columns can be listed in the the statement but the syntax doesn't show an optional comma or anything that would even hint at the syntax.

它说，mutliple列可以在语句中列出，但是语法不显示可选的逗号或任何提示语法的东西。

How should I write my SQL to drop multiple columns in one statement (if possible)?

如何编写SQL以在一条语句中删除多个列(如果可能的话)?

12 个解决方案

#1

alter table TableName
    drop column Column1, Column2

The syntax is

语法

DROP { [ CONSTRAINT ] constraint_name | COLUMN column } [ ,...n ] 

For MySQL

MySQL

alter table TableName
    drop Column1, drop Column2

#2

So summarizing

所以总结

Oracle:

Oracle:

ALTER TABLE table_name DROP (column_name1, column_name2);

MS SQL:

SQL女士:

ALTER TABLE table_name DROP COLUMN column_name1, column_name2

MySql:

MySql:

ALTER TABLE table_name DROP column_name1, DROP column_name2;

Postgre SQL

Postgre SQL

ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2;

#3

create table test (a int, b int , c int, d int);
alter table test drop column b, d;

Be aware that DROP COLUMN does not physically remove the data, and for fixed length types (int, numeric, float, datetime, uniqueidentifier etc) the space is consumed even for records added after the columns were dropped. To get rid of the wasted space do ALTER TABLE ... REBUILD.

注意，DROP列不会物理地删除数据，对于固定长度类型(int、numeric、float、datetime、uniqueidentifier)，即使删除列后添加的记录，也会占用空间。为了摆脱浪费的空间，改变一下桌子……重建。

#4

This may be late, but sharing it for the new users visiting this question. To drop multiple columns actual syntax is

这可能会很晚，但是请为访问这个问题的新用户共享它。要删除多个列，实际的语法是

alter table tablename drop column col1, drop column col2 , drop column col3 ....

So for every column you need to specify "drop column" in Mysql 5.0.45.

因此，对于每个列，都需要在Mysql 5.0.45中指定“drop column”。

#5

The Syntax as specified by Microsoft for the dropping a column part of an ALTER statement is this

Microsoft为删除ALTER语句的列部分指定的语法是这样的

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

Notice that the [,...n] appears after both the column name and at the end of the whole drop clause. What this means is that there are two ways to delete multiple columns. You can either do this:

(注意,…n]同时出现在列名后面和整个drop子句的末尾。这意味着有两种方法可以删除多个列。你可以这样做:

ALTER TABLE TableName
    DROP COLUMN Column1, Column2, Column3

or this

或者这个

ALTER TABLE TableName
    DROP 
        COLUMN Column1,
        COLUMN Column2,
        COLUMN Column3

This second syntax is useful if you want to combine the drop of a column with dropping a constraint:

如果您希望将列的删除与删除约束相结合，那么第二个语法是有用的:

ALTER TBALE TableName
    DROP
        CONSTRAINT DF_TableName_Column1,
        COLUMN Column1;

When dropping columns SQL Sever does not reclaim the space taken up by the columns dropped. For data types that are stored inline in the rows (int for example) it may even take up space on the new rows added after the alter statement. To get around this you need to create a clustered index on the table or rebuild the clustered index if it already has one. Rebuilding the index can be done with a REBUILD command after modifying the table. But be warned this can be slow on very big tables. For example:

当删除列时，SQL服务器不会回收被删除列占用的空间。对于以内联方式存储在行的数据类型(例如int)，它甚至可能占用alter语句后添加的新行上的空间。要解决这个问题，您需要在表上创建集群索引，或者如果集群索引已经有的话，则重新构建集群索引。重建索引可以在修改表之后使用一个重建命令来完成。但要注意的是，这在非常大的桌子上可能会很慢。例如:

ALTER TABLE Test
    REBUILD;

#6

For MySQL (ver 5.6), you cannot do multiple column drop with one single drop-statement but rather multiple drop-statements:

对于MySQL (ver5.6)，不能使用一个drop-statement来执行多个列删除，而是使用多个drop-statement:

mysql> alter table test2 drop column (c1,c2,c3);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(c1,c2,c3)' at line 1
mysql> alter table test2 drop column c1,c2,c3;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'c2,c3' at line 1
mysql> alter table test2 drop column c1, drop column c2, drop c3;
Query OK, 0 rows affected (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 

BTW, drop <col_name> is shorthanded for drop column <col_name> as you can see from drop c3 above.

顺便说一下，drop 对于drop列 进行了缩写，您可以从上面的drop c3中看到。

#7

If it is just single column to delete the below syntax works

如果只是一个列，则删除下面的语法

ALTER TABLE tablename DROP COLUMN column1;

ALTER TABLE tablename DROP COLUMN column1;

For deleting multiple columns, using the DROP COLUMN doesnot work, the below syntax works

对于删除多个列，使用DROP列不工作，下面的语法工作。

ALTER TABLE tablename DROP (column1, column2, column3......);

改变表tablename DROP (column1, column2, column3……);

#8

alter table tablename drop (column1, column2, column3......);

#9

Generic:

泛型:

ALTER TABLE table_name 
DROP COLUMN column1,column2,column3;

E.g:

例句:

ALTER TABLE Student 
DROP COLUMN Name, Number, City;

#10

this query will alter the multiple column test it.

这个查询将修改多列测试它。

create table test(a int,B int,C int);

alter table test drop(a,B);

#11

ALTER table table_name Drop column column1, Drop column column2,Drop column column3;

for MySQL DB.

MySQL数据库。

Or you can add some column while altering in the same line:

或者你也可以在同样的行中添加一些列:

ALTER table table_name Drop column column1, ADD column column2 AFTER column7;

#12

Try following queries:

试试下面的查询:

alter table table_name add field_name data_type

or

或

alter table table_name drop column field_name

or

或

alter table table_name drop field_name