Solved

Alter SQL Statement

Posted on 2004-03-24
5
4,880 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 47

Accepted Solution

by:
schwertner earned 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Wrap Oraccle SQL*Plus executable Command 4 84
Consolidating oracle query results to a single line 8 60
How to free up undo space? 3 42
PL/SQL Two changes 7 29
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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

831 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