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

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

> 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.

On Sat, 09 Sep 2006 15:02:17 +0200, Dikkie Dik <nospam@nospam.org>
wrote:

>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
>
>> 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 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 replicating 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