Re: Help! How to delete a column?

Margaret Ngai wrote:
>
> I have added a column which I don't really want in a table. How can I
> delete it?
>
> ALTER TABLE does not support the DELETE function & Schema Manager doesn't
> have a remove option for a column.

Right, there is no drop column as such. You have to do something like this. If you have a table with columns a, b, c and you want to drop column b.

  create table new_table as (select a, c from old_table)   drop table old_table
  rename new_table to old_table

This will not copy over any triggers, etc. on the original table so you will have to recreate them.

Another way is to use a third-party application such as ERwin. You can reverse engineer the table (triggers, indexes, contraints -- the whole shabang), delete the column, change the name, remove or modify any triggers, etc. that use the deleted column, create the new table and then just INSERT from the old to the new. If you have such a tool, this is the best method.

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"