Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Posts
    58

    Unanswered: using a NULL field in a CREATE TABLE

    I would like to create a table based on results returned from a query (ie CREATE TABLE newtable AS (SELECT id AS "col1", NULL AS "col2" FROM oldtable))

    My problem is that you cannot set col2 as a NULL in a create table statement. I could just set it to a blank string, and update it to all nulls, but I was hoping their would be a way I can set col2 to all nulls in the create table statement. Any ideas?

  2. #2
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Simply add a column into the oldtable like this..

    ALTER TABLE oldtable ADD (new_col datatype);

    THEN, exeute your above statement.

    CREATE TABLE newtable AS SELECT id AS "col1", new_col AS "col2"
    FROM oldtable;
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  3. #3
    Join Date
    Feb 2002
    Posts
    58
    Thanks for the reply,

    I only have read-only access to the "oldtable" which is part of my reason for creating a new one. When I do create a new one, I can alter it all I want, but I thought it would be more efficent if I learned how to do it in the create table statement.

  4. #4
    Join Date
    Dec 2003
    Posts
    10

    Update the table

    First create the table wth blank string in col2 and then later update the field to NULL

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: using a NULL field in a CREATE TABLE

    Originally posted by WhiZa
    I would like to create a table based on results returned from a query (ie CREATE TABLE newtable AS (SELECT id AS "col1", NULL AS "col2" FROM oldtable))

    My problem is that you cannot set col2 as a NULL in a create table statement. I could just set it to a blank string, and update it to all nulls, but I was hoping their would be a way I can set col2 to all nulls in the create table statement. Any ideas?
    You could create an empty table and then insert values into it like

    INSERT INTO NEWTABLE (COL1) SELECT COL1 FROM OLDTABLE;

    In that case NEWTABLE.COL2 will have all default values (presumable NULLs).
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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