-
I have a table with data that I need to add a not null column to therefore I truncate the table and add the not null column
how do I get the original data back into the table,
the import seems to fail!
Thanks in a advance
-
you'll have a value in this column since it's a NOT NULL one, right ??
what you should do is :
1) add a column (but not NOT NULL)
2) update your new column
3) make it NOT NULL
you can even specify a default value when adding your new column, thus avoiding step 2, but only if all lines have the same value for your new column ...
-
drop new column;
make an import;
create table nw as select id,name from tb;
truncate tb;
add to tb column 'num';
insert into tb select id,name,'value' from nw;
Best wishes!
Dmitri
-
Or you just can add column with default value. And that's it.
And you do not need to truncate table.
Best wishes!
Dmitri
-
Couldn't you just assign a DEFAULT value to the no null column?
MH
I remember when this place was cool.
-
Originally posted by kgb
Or you just can add column with default value. And that's it.
And you do not need to truncate table.
Darn!! You beat me by one minute!!
Until next time...
MH
I remember when this place was cool.
-
What the problem?
SQL> create table qq (s1 number);
Table created.
SQL> insert into qq values('1');
1 row created.
SQL> alter table qq add(s2 number default 0 not null);
Table altered.
SQL> select * from qq;
S1 S2
---------- ----------
1 0
Originally posted by Mr.Hanky
Originally posted by kgb
Or you just can add column with default value. And that's it.
And you do not need to truncate table.
Darn!! You beat me by one minute!!
Until next time...
MH
Best wishes!
Dmitri
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|