我想编写一个SQL命令，在一个ALTER table语句中删除单个表中的多个列。
从MSDN的ALTER TABLE文档…
DROP {[CONSTRAINT] constraint_name | COLUMN column_name} 指定从表中删除constraint_name或column_name。如果兼容级别为65或更早，则不允许使用DROP COLUMN。可以列出多个列和约束。
它说可以在语句中列出多个列，但语法没有显示可选的逗号或任何暗示语法的东西。
我应该如何写我的SQL在一个语句中删除多个列(如果可能的话)?
当前回答
alter table tablename drop (column1, column2, column3......);
其他回答
Microsoft为删除ALTER语句中的列部分指定的语法如下所示
DROP
{
[ CONSTRAINT ]
{
constraint_name
[ WITH
( <drop_clustered_constraint_option> [ ,...n ] )
]
} [ ,...n ]
| COLUMN
{
column_name
} [ ,...n ]
} [ ,...n ]
注意[，…N]出现在列名之后和整个drop子句的末尾。这意味着有两种方法可以删除多个列。你可以这样做:
ALTER TABLE TableName
DROP COLUMN Column1, Column2, Column3
或者这个
ALTER TABLE TableName
DROP
COLUMN Column1,
COLUMN Column2,
COLUMN Column3
如果你想把删除列和删除约束结合起来，这第二个语法是有用的:
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:
ALTER TABLE Test
REBUILD;
对于MySQL(5.6版本)，你不能用一个drop-statement来执行多个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>
顺便说一句，drop <col_name>是drop column <col_name>的缩写，正如您可以从上面的drop c3中看到的那样。
ALTER table table_name Drop column column1, Drop column column2,Drop column column3;
MySQL数据库。
或者你可以添加一些列，同时改变在同一行:
ALTER table table_name Drop column column1, ADD column column2 AFTER column7;
如果只是单列删除，下面的语法是可行的
删除列:
对于删除多个列，使用DROP COLUMN不起作用，下面的语法可以
DROP (columnn1, column2, column3......);
postgis是
修改表table01，删除col1列，删除col2
