Professional Web Applications Themes

Insert without (NULL) - MySQL

Sorry if this has been asked and answered before, but can't seem to find a reference to it. I have a table with numerous columns. A simple insert statement like this: insert into chemp set number = '46-000000045', name = 'John Doe' works fine "except" all other columns have "(NULL)" as their value. Is there a way to insert a row, without explicitly setting every column value to '' and prevent this "default" value from being inserted. Thanks, Lee...

  1. #1

    Default Insert without (NULL)

    Sorry if this has been asked and answered before, but can't seem to
    find a reference to it.

    I have a table with numerous columns. A simple insert statement like
    this:

    insert into chemp set number = '46-000000045', name = 'John Doe'

    works fine "except" all other columns have "(NULL)" as their value.
    Is there a way to insert a row, without explicitly setting every
    column value to '' and prevent this "default" value from being
    inserted.

    Thanks,
    Lee
    Lee Guest

  2. #2

    Default Re: Insert without (NULL)

    I am not sure what you want.

    You insert a whole row. A row is always complete. So the other fields do
    have to have a value of some sort. If you do not specify a value for
    them, the default value is used. If no default value is specified in the
    table, NULL is the ultimate default and is used.

    Very new versions of MySQL seem to have a restrict policy that I am used
    to with other databases: If a row is set to NOT NULL and no default is
    given, a value must be specified during insert or the insert is
    rejected. Older versions of MySQL would insert the standard default
    value for the data type (0 for numbers, '' for strings)

    Best regards
     
    Dikkie Guest

  3. #3

    Default Re: Insert without (NULL)

    On Sat, 09 Sep 2006 15:02:17 +0200, Dikkie Dik <org>
    wrote:
     [/ref]

    Thanks Dikkie,
    Your statement "If no default value is specified in the table, NULL is
    the ultimate default and is used" was/is the key. I went back to my
    table creation routine and modified it to give each column a default
    value of "". Now when I insert a row with only a few of the column
    values specified, I get what I wanted.

    I'm in the process of converting some over 1100 BTrieve tables to
    MySQL so the table creation is scripted along with a "load data local
    infile...." statement to load the preliminary data. All was going
    well until I got to the part of ting our BTrieve insert
    statements. By going back and modifying the table create routine
    (based on your hint of default value), I'm good to go now - at least
    for the next few steps.

    Thanks
    Lee
    Lee Guest

  4. #4

    Default Re: Insert without (NULL)

    On Sat, 09 Sep 2006 12:45:09 GMT, Lee Peedin wrote: 

    Build your table with preferrable defaults for columns.

    --
    40. I will be neither chivalrous nor sporting. If I have an unstoppable
    superweapon, I will use it as early and as often as possible instead of
    keeping it in reserve.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

Similar Threads

  1. 500 NULL error on DB Insert (SQL Server)
    By jgmoore in forum Coldfusion Database Access
    Replies: 3
    Last Post: May 20th, 01:15 AM
  2. odbc insert null error
    By bflophil in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: February 18th, 10:09 PM
  3. INSERT Null value problem
    By Targa in forum ASP Database
    Replies: 8
    Last Post: May 15th, 03:50 AM
  4. ASP SQL Insert NULL Date Value
    By Chad S in forum ASP
    Replies: 5
    Last Post: February 19th, 07:33 PM
  5. How insert null in numerics fields?
    By Vilmar Braz„o de Oliveira in forum ASP Database
    Replies: 5
    Last Post: January 8th, 06:40 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139