Results 1 to 3 of 3
  1. #1
    Join Date
    May 2002
    Posts
    5

    Question Unanswered: How to deal with the not null fields when inserting data

    Hi there,

    Can anyone tell me how to deal with those not null fields when inserting data into a table?

    For example:

    insert tbl_test (fax)
    select fax_no from another_table, tbl_test
    where another_table_id != tbl_test_id

    tbl_test table has a couple of not null fields such as ssn, name, email.

    Your help would be highly appreciated!

    Art

  2. #2
    Join Date
    May 2002
    Location
    KOLKATA, INDIA
    Posts
    7
    So far I know it is not possible to insert into a table with values specified for only one column where there are few more not null columns are present.
    SO what you can do is,
    alter the table to have some default value to other not null fields in that table. Now you will be allowed to insert record with only one column value specified.

    create table tab1 (c1 number(5) not null,
    c2 char(10) not null,
    c3 varchar2(10),
    c4 varchar2(20) not null);

    SQL> create table tab1 (c1 char(5) not null,
    c2 number(5),
    c3 varchar2(10) not null);

    SQL> insert into tab1 values('AAAAA',10, 'LMNOP');

    1 row created.

    SQL> insert into tab1 values('AAAAB');
    insert into tab1 values('AAAAB')
    *
    ERROR at line 1:
    ORA-00947: not enough values


    SQL> insert into tab1(c1) values('AAAAB');
    insert into tab1(c1) values('AAAAB')
    *
    ERROR at line 1:
    ORA-01400: cannot insert NULL into ("MTT"."TAB1"."C3")

    SQL> alter table tab1 modify (c3 default 'Change It');

    Table altered.

    SQL> insert into tab1(c1) values('AAAAB');

    1 row created.

    SQL>

    Hope this will work.

    Santosh Sarkar

  3. #3
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    Try:

    insert tbl_test (fax,ssn,name,email)
    select fax_no, ' ', ' ', ' ' from another_table, tbl_test
    where another_table_id != tbl_test_id

    So you insert a space into the columns that cannot be NULL. (Or a 0 if its a number)

    Ruud
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •