Re: Reducing a column's width
Date: Thu, 25 Nov 1999 11:25:37 GMT
Message-ID: <383a9a45.27169767_at_read.news.globalnet.co.uk>
On Mon, 22 Nov 1999 19:43:00 GMT, newbie22_at_my-deja.com wrote:
>HI all,
>
>How can I change a column's width,
>where the column is not currently empty?
>
>You can't do it with ALTER TABLE,
>because the field is not empty.
>
Assuming oracle 8i:
SQL> create table fred ( a varchar2(50) );
Table created.
SQL> insert into fred values ( 'aaaaaaaaaaaaaaa' );
1 row created.
SQL> alter table fred modify a varchar2(30); alter table fred modify a varchar2(30)
*
ERROR at line 1:
ORA-01441: column to be modified must be empty to decrease column length
SQL> alter table fred add b varchar2(50);
Table altered.
SQL> update fred set b = a, a = null;
1 row updated.
SQL> select * from fred;
A
B
aaaaaaaaaaaaaaa
SQL> alter table fred modify a varchar2(30);
Table altered.
SQL> update fred set a = b, b = null;
1 row updated.
SQL> alter table fred drop column b;
Table altered. Received on Thu Nov 25 1999 - 12:25:37 CET