如何在 SQL Server 中使用单个 ALTER TABLE 语句删除多个列？

Question

提问by Jesse Webb

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

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

From MSDN's ALTER TABLE documentation...

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.

DROP { [CONSTRAINT] constraint_name | COLUMN column_name }
指定从表中删除constraint_name 或column_name。如果兼容级别为 65 或更早，则不允许使用 DROP COLUMN。可以列出多个列和约束。

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.

它说可以在语句中列出多个列，但语法不显示可选的逗号或任何甚至暗示语法的东西。

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

我应该如何编写 SQL 以在一个语句中删除多个列（如果可能）？

Answer 1

回答by Alex Aza

For SQL Server:

对于 SQL Server：

ALTER TABLE TableName
    DROP COLUMN Column1, Column2;

The syntax is

语法是

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

For MySQL:

对于 MySQL：

ALTER TABLE TableName
    DROP COLUMN Column1,
    DROP COLUMN Column2;

or like this¹:

或像这样¹：

ALTER TABLE TableName
    DROP Column1,
    DROP Column2;

¹The word COLUMNis optionaland can be omitted, except for RENAME COLUMN(to distinguish a column-renaming operation from the RENAMEtable-renaming operation). More info here.

¹这个词COLUMN是可选的，可以省略，除了RENAME COLUMN（为了区分列重命名操作和RENAME表重命名操作）。更多信息在这里。

Answer 2

回答by Denis Rozhnev

Summarizing

总结

Oracle:

甲骨文：

ALTER TABLE table_name DROP (column_name1, column_name2);

MS SQL Server:

微软 SQL 服务器：

ALTER TABLE table_name DROP COLUMN column_name1, column_name2

MySQL:

MySQL：

ALTER TABLE table_name DROP column_name1, DROP column_name2;

PostgreSQL

ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2;

Be aware

意识到

DROP COLUMNdoes not physicallyremove the data for some DBMS. E.g. for MS SQL. For fixed length types (int, numeric, float, datetime, uniqueidentifieretc) 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 COLUMN不会物理删除某些 DBMS 的数据。例如对于 MS SQL。对于固定长度类型（int、numeric、float、datetime、uniqueidentifier等），即使在删除列之后添加的记录也会消耗空间。摆脱浪费的空间呢ALTER TABLE ... REBUILD。

Answer 3

回答by Remus Rusanu

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 COLUMN 不会物理删除数据，对于固定长度类型（int、numeric、float、datetime、uniqueidentifier 等），即使对于在删除列之后添加的记录，也会消耗空间。摆脱浪费的空间呢ALTER TABLE ... REBUILD。

Answer 4

回答by MANISH ZOPE

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 中指定“删除列”。

Answer 5

回答by Martin Brown

The Syntax as specified by Microsoft for the dropping a column part of an ALTERstatement 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 Sever 不会回收被删除的列占用的空间。对于内联存储在行中的数据类型（例如 int），它甚至可能占用在 alter 语句之后添加的新行上的空间。为了解决这个问题，您需要在表上创建一个聚集索引，或者如果已经有一个聚集索引，则重建该聚集索引。可以在修改表后使用 REBUILD 命令重建索引。但请注意，这在非常大的桌子上可能会很慢。例如：

ALTER TABLE Test
    REBUILD;

Answer 6

回答by CSY

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

对于 MySQL（5.6 版），您不能使用单个drop语句执行多列删除，而是使用多个drop语句：

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 c3above.

顺便说一句，正如您从上面看到的那样，drop <col_name>是简写的。drop column <col_name>drop c3

Answer 7

回答by kk250040

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

如果只是单列删除以下语法有效

ALTER TABLE tablename DROP COLUMN column1;

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

对于删除多列，使用DROP COLUMN不起作用，以下语法有效

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

Answer 8

回答by Neelima

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

Answer 9

回答by Chirag Thakar

Generic:

通用的：

ALTER TABLE table_name 
DROP COLUMN column1,column2,column3;

E.g:

例如：

ALTER TABLE Student 
DROP COLUMN Name, Number, City;

Answer 10

回答by naveen vaishnav

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;

如何在 SQL Server 中使用单个 ALTER TABLE 语句删除多个列？

提问by Jesse Webb

回答by Alex Aza

For SQL Server:

对于 SQL Server：

For MySQL:

对于 MySQL：

回答by Denis Rozhnev

Summarizing

总结

Be aware

意识到

回答by Remus Rusanu

回答by MANISH ZOPE

回答by Martin Brown

回答by CSY

回答by kk250040

回答by Neelima

回答by Chirag Thakar

回答by naveen vaishnav

相关推荐

最近更新

标签

如何在 SQL Server 中使用单个 ALTER TABLE 语句删除多个列？

提问by Jesse Webb

回答by Alex Aza

For SQL Server:

对于 SQL Server：

For MySQL:

对于 MySQL：

回答by Denis Rozhnev

Summarizing

总结

Be aware

意识到

回答by Remus Rusanu

回答by MANISH ZOPE

回答by Martin Brown

回答by CSY

回答by kk250040

回答by Neelima

回答by Chirag Thakar

回答by naveen vaishnav

相关推荐

SQL 使用子查询更新 postgres 中的表行

SQL 计算每个值的所有重复项

如何创建一个 SQL Server 函数来返回一个 int？

SQL 在 Postgres 中获取表的最后一条记录

相关推荐

最近更新

标签