Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4890
  • Last Modified:

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?
0
paulj1999
Asked:
paulj1999
1 Solution
 
schwertnerCommented:
alter table x modify x varchar2(nnn) nullable;
will not damage the data.
Only if you make the length of the column lower and truncation ocurs the operation will be denied.
0
 
Helena Markováprogrammer-analystCommented:
No, the data will not be erased ...
0
 
OMC2000Commented:
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
0
 
n4nazimCommented:
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
0
 
rajnadimpalliCommented:
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
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now