Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to delete a column from the table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
keerthi

New User


Joined: 23 Mar 2005
Posts: 25

PostPosted: Tue May 24, 2005 12:19 pm    Post subject: How to delete a column from the table
Reply with quote

Can we delete a column from the table if yes wat is the query

Thanks in advance,
keerthi.
Back to top
View user's profile Send private message

MGIndaco

Active User


Joined: 10 Mar 2005
Posts: 432
Location: Milan, Italy

PostPosted: Tue May 24, 2005 3:30 pm    Post subject:
Reply with quote

Do you want delete all the record of a table or you want delete the column?
If you want delete all the rows of a column you must update all the record for that column erasing so its value.
If you want delete the column you must ALTER the table and DROP the column you desire as you can see below:
Code:
ALTER TABLE mytable
DROP COLUMN mycolumn

But it's a danger solution if you don't know its refereces...
Look before you drop if it is a PRIMARY or FOREING KEY!
Back to top
View user's profile Send private message
David P

Active User


Joined: 11 Apr 2005
Posts: 106
Location: Cincinnati Ohio

PostPosted: Tue May 24, 2005 6:00 pm    Post subject:
Reply with quote

Hi MGIndaco,

Correct me if I am wrong but I believe it won't allow to drop a column if it is primary key.

regards,
david.
Back to top
View user's profile Send private message
raguibmm

New User


Joined: 16 Mar 2005
Posts: 4

PostPosted: Tue May 24, 2005 7:50 pm    Post subject:
Reply with quote

ya it won 't allow
Back to top
View user's profile Send private message
MGIndaco

Active User


Joined: 10 Mar 2005
Posts: 432
Location: Milan, Italy

PostPosted: Tue May 24, 2005 8:36 pm    Post subject:
Reply with quote

It's for this that I ask a control before process this alter icon_wink.gif
Back to top
View user's profile Send private message
babuprasad_g
Currently Banned

New User


Joined: 26 May 2005
Posts: 45
Location: gurgaon

PostPosted: Thu Jun 02, 2005 4:18 pm    Post subject: Re: How to delete a column from the table
Reply with quote

Hi,

Better way is just Create Another Table and load The records from your previous table Then u can Drop Your table.

I hope this is the right way.
Back to top
View user's profile Send private message
jeroznishanth

New User


Joined: 21 Nov 2007
Posts: 8
Location: Bangalore

PostPosted: Tue Dec 04, 2007 2:37 pm    Post subject:
Reply with quote

Code:
 ALTER TABLE CAT_MAIN34
DROP COLUMN REMARKS;


MY TABLE IS CAT_MAIN34 AND THE COLUMN WHICH I WAN T TO REMOVE IS REMARKS. AND ITS NOT A PRIMARY OR FORIEGN KEY...

DINT WORK WITH ME.... PLZ HELP ME....
Back to top
View user's profile Send private message
muthuvel

Active User


Joined: 29 Nov 2005
Posts: 217
Location: Chennai

PostPosted: Tue Dec 04, 2007 3:29 pm    Post subject: Reply to: How to delete a column from the table
Reply with quote

jeroznishanth,
Welcome to the Forum.

Do you have privilege to alter the table bcos it will be with DBAs only .Is it throwing any error ?
Back to top
View user's profile Send private message
jeroznishanth

New User


Joined: 21 Nov 2007
Posts: 8
Location: Bangalore

PostPosted: Wed Dec 05, 2007 2:21 pm    Post subject:
Reply with quote

hai muthu,

actually i am learning mainframes nw... I created a file... so obviously i will hav all the rights... right?... if i am wrong just tell me...


Code:
ALTER TABLE CAT_MAIN34
DROP COLUMN REMARKS;


i gave this SQL in QMF... and it gave the error

Code:
SQL error at or before REMARKS


i hope that u can helo me out....

icon_smile.gif
Back to top
View user's profile Send private message
jeroznishanth

New User


Joined: 21 Nov 2007
Posts: 8
Location: Bangalore

PostPosted: Wed Dec 05, 2007 2:23 pm    Post subject:
Reply with quote

Quote:
I created a file


that is " i created table".. icon_wink.gif
Back to top
View user's profile Send private message
naveensrimf

New User


Joined: 04 Oct 2005
Posts: 31

PostPosted: Thu Jul 28, 2011 5:43 pm    Post subject: Reply to: How to delete a column from the table
Reply with quote

which version you are using? Upto Version 8 you can't drop the column in Table. Version 9 onwards you can drop the column from table.

Note: Before Drop the column you have analysie the impact of the Column.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10375
Location: italy

PostPosted: Thu Jul 28, 2011 6:21 pm    Post subject: Reply to: How to delete a column from the table
Reply with quote

do You realize You replied to a three years and a half old topic ?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us