Re: Change Column of Tables ... (HELP!!!)

From: Marc Blum <marc_at_marcblum.de>
Date: Mon, 15 Oct 2001 07:18:57 GMT
Message-ID: <3bca8b90.271530@news.online.de>

On 14 Oct 2001 20:37:32 -0700, jkyf0131_at_hotmail.com (JK Yao) wrote:

>dear all,
>
>after I create my database and ran for a while, I found out that I
>create wrong columns. Can I change the column name or even add or
>drop a column?
>How to do? Please help. Thanks a lot.
>
>reagards,
>JK

Hi,

---

in 8.1.x you can easily drop a column:

(1) add a new column:

ALTER <table_name>

   ADD (<new_column> <datatype, constraints,defaults etc.>)

example:

ALTER TABLE emp

   ADD (empweight NUMBER);

(2) migrate data:

UPDATE <table_name>

    SET <new_column> = <old_column>;

example:

UPDATE emp

   SET empweight = epmwieght;
 /* old column is only missspelled ;-) */

(3) drop old column:

ALTER TABLE <table_name>

   DROP <old_column>;

example:

ALTER TABLE emp

   DROP COLUMN epmwieght;

(4) Add constraints to the new column:

ALTER TABLE <table_name>

   MODIFY <new_column> <constraints defaults etc.>;

example:

ALTER TABLE emp

   MODIFY empweight NOT NULL;

(5) compile all dependent objects

---

In Oracle 7 and 8.0.x you have to rename the table, create the correct table and migrate the data.

:-(

---

Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Mon Oct 15 2001 - 02:18:57 CDT