none
Can I drop more than one column? RRS feed

  • Question

  •  

    Hi,

     

    Can I drop more than one cloumn?

     

    ALTER TABLE [tablename] DROP COLUMN [col1] ... this just drop one column.

     

    how can I drop more than one column in same query???

     

     

     

    Thank.

    Monday, September 1, 2008 8:48 AM

All replies

  • Hi,

     

    Simply ennumerate the columns to be dropped.

     

    See this:

     

    Code Snippet

    create table #test(id int, column1 varchar(255), column2 varchar(255))

    insert into #test values(1,'first column', 'second column')

    select id,column1,column2 from #test

    --drop 2 columns.

    alter table #test drop column column1, column2

    select id from #test

    --select id, column1 from #test --this line will give an error

    drop table #test

     

     

     

    Monday, September 1, 2008 9:03 AM
  •  

    can't ... ERROR

     

    ---------------------------
    Microsoft Visual Studio
    ---------------------------
    SQL Execution Error.

    Executed SQL statement: alter table abc drop column Col2,Col4
    Error Source: SQL Server Compact ADO.NET Data Provider
    Error Message: There was an error parsing the query. [ Token line number = 1,Token line offset = 33,Token in error = , ]
    ---------------------------
    OK   Help  
    ---------------------------

     

     

     

    Monday, September 1, 2008 9:15 AM
  • I see you are using SQL Server CE.

    You can only ADD multiples columns with CE. Not delete them. See extract from SQL Reference for SQL Server Mobile for ALTEr TABLE:

     

    Code Snippet
    ALTER TABLE table_name{ [ ALTER COLUMN column_name    {DROP DEFAULT    | SET DEFAULT constant_expression    | IDENTITY [ ( seed , increment ) ]   } | ADD    { < column_definition > | < table_constraint > } [ ,...n ] | DROP    { [ CONSTRAINT ] constraint_name    | COLUMN column }] }< column_definition > ::=    { column_name data_type }    [ [ DEFAULT constant_expression ]       | IDENTITY [ ( seed , increment ) ]    ]    [ROWGUIDCOL]   [ < column_constraint > ] [ ...n ] ]< column_constraint > ::=    [ NULL | NOT NULL ]    [ CONSTRAINT constraint_name ]    {       | { PRIMARY KEY | UNIQUE }       | REFERENCES ref_table [ (ref_column) ]       [ ON DELETE { CASCADE | NO ACTION } ]       [ ON UPDATE { CASCADE | NO ACTION } ]    }< table_constraint > ::=    [ CONSTRAINT constraint_name ]    { [ { PRIMARY KEY | UNIQUE }       { ( column [ ,...n ] ) }       | FOREIGN KEY         ( column [ ,...n ] )        REFERENCES ref_table [ (ref_column [ ,...n ] ) ]       [ ON DELETE { CASCADE | NO ACTION } ]       [ ON UPDATE { CASCADE | NO ACTION } ]    }

     

     

    Monday, September 1, 2008 9:39 AM