OraFAQ Forum: SQL & PL/SQL » alter table drop column

Home » SQL & PL/SQL » SQL & PL/SQL » alter table drop column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

alter table drop column [message #186497]

Tue, 08 August 2006 04:34

steffeli
Messages: 112
Registered: July 2006

Senior Member

Hello

Something is wrong with my syntax but I can't find out what's wrong.

alter table test
drop column prod1, prod2, prod3

The code works when I delete just 1 column. Any idea?

Thanks
Stefan

Report message to a moderator

Re: alter table drop column [message #186500 is a reply to message #186497]

Tue, 08 August 2006 04:46

Maaher
Messages: 7065
Registered: December 2001

Senior Member

I think you need individual alter table statements (although I didn't check the manuals Very Happy

).

[EDIT]
I better did. It is possible

ALTER TABLE thetable DROP (col1,col2,...);

[EDIT 2]
And again, I edited this too late. navkrish and Littlefoot beat me.

MHE

[Updated on: Tue, 08 August 2006 04:54]

Report message to a moderator

Re: alter table drop column [message #186501 is a reply to message #186497]

Tue, 08 August 2006 04:48

navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA

Senior Member

It is possible...
Here is the code.

   alter table test drop(prod1, prod2, prod3)

Naveen

Report message to a moderator

Re: alter table drop column [message #186502 is a reply to message #186501]

Tue, 08 August 2006 04:52

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

And the manual is here; basically, when dropping a list of columns, you can't have a COLUMN keyword.

Report message to a moderator

Re: alter table drop column [message #186503 is a reply to message #186500]

Tue, 08 August 2006 04:56

amit_kiran
Messages: 50
Registered: July 2006
Location: UK

Member

You cannot drop all the columns at a single point of time.

But as an alternate you can set the columns as ununsed.

ALTER TABLE TEST SET UNUSED (prod1, prod2);
ALTER TABLE TEST DROP UNUSED COLUMNS;

This will drop prod1, prod2 columns from a table.
You have to kep one column as used. You cannot mark all the columns as unused.

Razz

Report message to a moderator

Re: alter table drop column [message #186505 is a reply to message #186497]

Tue, 08 August 2006 05:04

navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA

Senior Member

amit_kiran,
If you want to drop all the column, it is better to drop the table itself...
but still if you try to drop all the columns of a table,then it will throw 'ORA-12983: cannot drop all columns in a table'

I guess, Stefan wants to drop more than one column at a time and not all the columns of table test.

Maheer, let me know if my comment is valid.

Naveen

[Updated on: Tue, 08 August 2006 05:08]

Report message to a moderator

Re: alter table drop column [message #186506 is a reply to message #186505]

Tue, 08 August 2006 05:07

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

Who is Maheer?

Report message to a moderator

Re: alter table drop column [message #186507 is a reply to message #186505]

Tue, 08 August 2006 05:07

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Hey, you corrected me, Naveen Wink

. I think you're right. A table without columns wouldn't make sense anyway. But amit_kiran stated the same: you cannot drop all columns, nor can you set them all to unused.

MHE

Report message to a moderator

Re: alter table drop column [message #186508 is a reply to message #186506]

Tue, 08 August 2006 05:08

Maaher
Messages: 7065
Registered: December 2001

Senior Member

JSI2001 wrote on Tue, 08 August 2006 12:07

Who is Maheer?

My dark side (insert evil laughter here)

MHE

Report message to a moderator

Re: alter table drop column [message #186510 is a reply to message #186508]

Tue, 08 August 2006 05:09

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

Mwahahahaha Very Happy

Report message to a moderator

Re: alter table drop column [message #186512 is a reply to message #186505]

Tue, 08 August 2006 05:11

amit_kiran
Messages: 50
Registered: July 2006
Location: UK

Member

Yes i do agree with your statement.

ALTER TABLE TEST DROP(prod1, prod2, prod3);

this statement is valid only if you have one more column name
(prod4 NUMBER) in your table;

Correct me if i am on the wrong path..

Report message to a moderator

Previous Topic:	not worked concat_all function
Next Topic:	Response of the Select statement is extreme slow!!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 16:58:43 CST 2024