asked on

Alter SQL Statement

If I have a table x w/ a column called DL that's not nullable and of type varchar2, if I did the following alter statement, "alter table x modify x varchar2 nullable", will this erase my data?

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Helena Marková

No, the data will not be erased ...

OMC2000

assuming your original table has column dl column definition as varchar2(10) not null

statement

alter table x modify dl varchar2(10) null

won't erase your data.

you can check it yourself with a dummy table and data

n4nazim

Hi,

Considering yr statement that the column DL is NOT NULL. Now if u make if NULLable, the data wont be erased. U can try this with the sample data/scripts i have pasted below:

create table nztab1 ( a varchar2(10) not null);

insert into nztab1 values ( '1' );
insert into nztab1 values ( '2' );
insert into nztab1 values ( '3' );

alter table nztab1 modify a varchar2(10) null

select * from nztab1

After this select statement u can see that the data is still there.

Rgds,
NHM

rajnadimpalli

If I were you..I will just try it and see...what happens...

11:45:44 SQL> desc demo
Name Null? Type
----------------------------- -------- --------------------
COL1 NOT NULL VARCHAR2(10)
LOW_VALUE CHAR(4)
COL2 NUMBER
COL3 CHAR(2)

11:45:47 SQL> select * from demo;

COL1 LOW_ COL2 CO
---------- ---- ---------- --
John 2020 LA
Mark 5060 MI
BOND 4500 VA

3 rows selected.

11:45:50 SQL> alter table demo modify (COL1 varchar2(10) NULL);

Table altered.

11:46:19 SQL> desc demo
Name Null? Type
----------------------------- -------- --------------------
COL1 VARCHAR2(10)
LOW_VALUE CHAR(4)
COL2 NUMBER
COL3 CHAR(2)

11:46:24 SQL> select * from demo;

COL1 LOW_ COL2 CO
---------- ---- ---------- --
John 2020 LA
Mark 5060 MI
BOND 4500 VA

3 rows selected.

11:46:28 SQL>

And if you really want to erase data in One column and make it NULLABLE..then

11:49:52 SQL> alter table demo drop column col1;

Table altered.

11:50:06 SQL> desc demo;
Name Null? Type
----------------------------- -------- --------------------
LOW_VALUE CHAR(4)
COL2 NUMBER
COL3 CHAR(2)

11:50:15 SQL> alter table demo add (COL1 VARCHAR2(10) NULL);

Table altered.

11:50:20 SQL> desc demo;
Name Null? Type
----------------------------- -------- --------------------
LOW_VALUE CHAR(4)
COL2 NUMBER
COL3 CHAR(2)
COL1 VARCHAR2(10)

11:50:23 SQL> select * from demo;

LOW_ COL2 CO COL1
---- ---------- -- ----------
2020 LA
5060 MI
4500 VA

3 rows selected.

11:50:27 SQL>

Or you can do ...UPDATE DEMO set COL1=NULL;
...
..

-R