Home » SQL & PL/SQL » SQL & PL/SQL » alter table drop column
alter table drop column [message #186497] Tue, 08 August 2006 04:34 Go to next message
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
Re: alter table drop column [message #186500 is a reply to message #186497] Tue, 08 August 2006 04:46 Go to previous messageGo to next message
Maaher
Messages: 7062
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 Go to previous messageGo to next message
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
Re: alter table drop column [message #186502 is a reply to message #186501] Tue, 08 August 2006 04:52 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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.
Re: alter table drop column [message #186503 is a reply to message #186500] Tue, 08 August 2006 04:56 Go to previous messageGo to next message
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
Re: alter table drop column [message #186505 is a reply to message #186497] Tue, 08 August 2006 05:04 Go to previous messageGo to next message
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 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Who is Maheer?
Re: alter table drop column [message #186507 is a reply to message #186505] Tue, 08 August 2006 05:07 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: alter table drop column [message #186508 is a reply to message #186506] Tue, 08 August 2006 05:08 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
JSI2001 wrote on Tue, 08 August 2006 12:07

Who is Maheer?

My dark side (insert evil laughter here) http://www.orafaq.com/forum/fa/940/0/

MHE
Re: alter table drop column [message #186510 is a reply to message #186508] Tue, 08 August 2006 05:09 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Mwahahahaha Very Happy Laughing
Re: alter table drop column [message #186512 is a reply to message #186505] Tue, 08 August 2006 05:11 Go to previous message
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..

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


Current Time: Sat Dec 10 20:38:28 CST 2016

Total time taken to generate the page: 0.07589 seconds