How should i drop multiple columns in redshift database?

Question

Hello there i am trying to drop columns from my table specifically in Amazon Redshift Database i have tried "Alter Table ABC drop column C1,drop column C2" and "Alter Table ABC drop column C1,C2)" but it show error and didn't execute

Answer 1

From Redshift's ALTER TABLE documentation, you cannot drop several columns at once, cf:

ALTER TABLE table_name
{
ADD table_constraint |
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ] |
OWNER TO new_owner |
RENAME TO new_name |
RENAME COLUMN column_name TO new_name |
ADD [ COLUMN ] column_name column_type
[ DEFAULT default_expr ]
[ ENCODE encoding ]
[ NOT NULL | NULL ] |
DROP [ COLUMN ] column_name [ RESTRICT | CASCADE ] }

where table_constraint is:

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] )  |
PRIMARY KEY ( column_name [, ... ] ) |
FOREIGN KEY (column_name [, ... ] )
REFERENCES  reftable [ ( refcolumn ) ]}

Therefore, I would recommend you to proceed as follows:

ALTER TABLE ABC DROP COLUMN C1;
ALTER TABLE ABC DROP COLUMN C2;