DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Not Null

  1. #1
    Join Date
    Jul 2001
    Posts
    181

    Wink

    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

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    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 ...

  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    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

  4. #4
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    Or you just can add column with default value. And that's it.
    And you do not need to truncate table.
    Best wishes!
    Dmitri

  5. #5
    Join Date
    Jan 2001
    Posts
    3,134

    Lightbulb

    Couldn't you just assign a DEFAULT value to the no null column?

    MH
    I remember when this place was cool.

  6. #6
    Join Date
    Jan 2001
    Posts
    3,134
    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.

  7. #7
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    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
  •  


Click Here to Expand Forum to Full Width