Improve company productivity with a Business Account.Sign Up

x
?
Solved

Alter SQL Statement

Posted on 2004-03-24
5
Medium Priority
?
4,892 Views
Last Modified: 2012-05-05
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
Comment
Question by:paulj1999
5 Comments
 
LVL 48

Accepted Solution

by:
schwertner earned 1000 total points
ID: 10665596
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
 
LVL 22

Expert Comment

by:Helena Marková
ID: 10665601
No, the data will not be erased ...
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 10665605
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
 
LVL 2

Expert Comment

by:n4nazim
ID: 10665712
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
 
LVL 3

Expert Comment

by:rajnadimpalli
ID: 10669340
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question