How do I drop a column from a table in sybase 11? [Archive]

View Full Version : How do I drop a column from a table in sybase 11?

Jarelow

04-09-01, 12:59

Can anyone tell me how to frop a column from a sybase table.

I tried to do: alter table TABLENAME drop COLUMNNAME

but it didn't work.

It should be as simple as that right

Thank you all for replies Nick

Bret Halford

04-09-01, 19:44

Perhaps it "should be", but it isn't. ALTER TABLE DROP COLUMN isn't supported until ASE 12.0. Although the server will accept the syntax (not sure why it isn't in your case), the command is not supported prior to ASE 12.0 and had some bugs in it that were known to cause corruption of the table (which is why it was never documented or supported).

Prior to ASE 12.0, you could select all the desired columns into a new table, drop the original table, and use sp_rename to give the new table the old table's name.

________________
bret

Fuzzy

04-09-01, 21:18

Nope. drop column is not part of the SQL92 entry level standard (which I think is what 11.0.x and 11.5.x use (not sure about 11.9)). ASE 12 supports this, so upgrade. If that's not possible, use the rename-recreate-insert-drop approach.

________________
Ciao Fuzzy

Jarelow

04-10-01, 06:33

Hi!

select into works but is a bit complicated since I have to do this on many tables but I guess I have to do it.

But I think alter table was working before loading the patch SWR 9218.

Exact version: Adaptive Server Enterprise/11.9.2.3/1159/P/SWR 9218 ESD 3/Sun_svr4/OS 5.5.1/FBO/Tue Sep 5 12:20:58 2000

Thank ones again for the replies
Nicklas

naresh_kusunam

04-09-04, 17:23

Hello,
What you can do is copy all the columns except the one which you want to drop using

select (.......) into new_table from old_table
then you can change the name of thnew_table using sp_rename 'old_table', new_table

i am sure this will work.

---
Just trying to survive.