IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to delete a column from the table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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
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
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
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: Canada

PostPosted: Tue Dec 04, 2007 3:29 pm
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
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
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: 34

PostPosted: Thu Jul 28, 2011 5:43 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10833
Location: italy

PostPosted: Thu Jul 28, 2011 6:21 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Delete file row if blanks in the firs... DFSORT/ICETOOL 5
No new posts volume mass delete RMM JCL & VSAM 2
No new posts Split a record with data in a differe... DFSORT/ICETOOL 8
No new posts Infosphere Optim - unable to save Col... IBM Tools 0
No new posts Read a flat file and update DB2 table JCL & VSAM 2
Search our Forums:

Back to Top