Home » SQL & PL/SQL » SQL & PL/SQL » how to delete the particular column
how to delete the particular column [message #189504] Thu, 24 August 2006 22:51 Go to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi pals

i have 5 columns such as
SQL> desc stu;
 Name                            Null?    Type
 ------------------------------- -------- ----
 RNO                                      NUMBER
 M1                                       NUMBER
 M2                                       NUMBER
 M3                                       NUMBER
 TOT                                      NUMBER


i have inserted the data's in all the columns. now i want to delete a particular column. how to do this.

Re: how to delete the particular column [message #189515 is a reply to message #189504] Fri, 25 August 2006 00:11 Go to previous messageGo to next message
srinivas.dd
Messages: 2
Registered: April 2006
Location: hyderabad
Junior Member

alter table <table_name> drop column <column_name>;
Re: how to delete the particular column [message #189571 is a reply to message #189515] Fri, 25 August 2006 02:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, if you just want to remove the data, and not the column
UPDATE stu SET m2 = null;
Re: how to delete the particular column [message #189577 is a reply to message #189504] Fri, 25 August 2006 02:59 Go to previous messageGo to next message
youthsen
Messages: 45
Registered: August 2006
Member
hi srinivas.dd

it is not working

SQL> alter table stu drop column tot;
alter table stu drop column tot
                     *
ERROR at line 1:
ORA-00905: missing keyword


SQL> alter table stu drop_column tot;
alter table stu drop_column tot
                *
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option


i am using personal oracel 8.0 for win95.

how to solve this problem.
Re: how to delete the particular column [message #189596 is a reply to message #189577] Fri, 25 August 2006 03:31 Go to previous messageGo to next message
pamrajrajeev
Messages: 9
Registered: July 2006
Junior Member
Hai,

I Think it will work only from oracle 9i onwards. Just check it out.

to drop a particular column,
alter table <tablename> drop column <columnname>;

to drop list of columns,

alter table <tablename> drop columnanme1,columnnane2,....;

note: column keyword is not used


regards

Rajeev
Re: how to delete the particular column [message #189605 is a reply to message #189596] Fri, 25 August 2006 04:10 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Why don't you people post version information? It avoids solutions you cannot use. In your case I see two options:
1. use a view that selects all but the column you don't want to see.
2. create a new table using 'create table as select', drop the old table, rename the new table to the name of the original table (although I don't know whether you can rename a table in 8.0).

And then there's option 0:

search the board.
Re: how to delete the particular column [message #189612 is a reply to message #189605] Fri, 25 August 2006 04:16 Go to previous messageGo to next message
srinivas.dd
Messages: 2
Registered: April 2006
Location: hyderabad
Junior Member

hi, the drop column option was introduced only in the version 8i..before this version, there was no method to drop the column.

As u r saying that u using 8.0, the only method to do is the above one.
Re: how to delete the particular column [message #324092 is a reply to message #189571] Fri, 30 May 2008 15:11 Go to previous messageGo to next message
renu4dwh
Messages: 24
Registered: April 2008
Location: Pune
Junior Member
JRowbottom wrote on Fri, 25 August 2006 02:48
Or, if you just want to remove the data, and not the column
UPDATE stu SET m2 = null;



yeah i found your post useful. This this is what i was asked in an interview
Re: how to delete the particular column [message #324095 is a reply to message #324092] Fri, 30 May 2008 15:27 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who ask question about version 8.0? Surely a fool.

Maarten, RENAME already existed in version 7 for sure, I don't clearly remember before but I think it was already there in version 5.

Regards
Michel
Previous Topic: collections and Records VARRAY
Next Topic: Ora-03106 fatal two task communication protocol error
Goto Forum:
  


Current Time: Sun Dec 04 04:43:53 CST 2016

Total time taken to generate the page: 0.09456 seconds