I'm surprised nobody mentioned that you can specify the columns to be
inserted in the query:
INSERT INTO test(a, c, d) VALUES (1, 2 3);
(Note that `b` is missing it `a, c, d`. It will take the default value,
which will be NULL, unless a different default was specified explicitly in
the CREATE TABLE statement.)
It's usually preferable to specify column names in an INSERT query
explicitly, because it makes it easier to see what the values are supposed
to mean. It prevents mistakes like swapping the meaning of two adjacent
columns, or inserting a phone number in an email field, and things like
that.
On Tue, Jun 27, 2017 at 10:24 PM, David Raymond <david.raym...@tomtom.com>
wrote:
> If you have to provide 4 values then the way you can use null to do that
> is to add in a trigger to set the default, since NULL _is_ a value and _is_
> legal for that field.
>
> CREATE TRIGGER test_populate_b
> AFTER INSERT ON test
> WHEN new.b is null
> BEGIN
> UPDATE test
> SET b = '-'
> WHERE rowid = new.rowid;
> END;
>
> INSERT INTO test VALUES ('field a', NULL, 'field c', 'field d');
>
> a b c d
> ---------- ---------- ---------- ----------
> field a - field c field d
>
> -----Original Message-----
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Simon Slavin
> Sent: Tuesday, June 27, 2017 4:08 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] INSERT ... VALUES / want to "skip" default values
>
>
>
> On 27 Jun 2017, at 8:13pm, Robert M. Münch <robert.mue...@saphirion.com>
> wrote:
>
> > CREATE TABLE test(a, b DEFAULT "-", c, d)
> >
> > Now I would like to use
> >
> > INSERT VALUES(a,?,c,d)
> >
> > Where ? is something that the default value is used and not the provided
> value. Is this possible at all?
>
> You provide the text "NULL" (not in any quotes) for that value:
>
> INSERT INTO test VALUES(12, NULL, 84, 'endomorph')
>
> If you’ve set up a statement with parameters …
>
> INSERT INTO test VALUES(?1, ?2, ?3, ?4)
>
> … you can leave that paramater unbound (all parameters are bound to NULL
> by default) or you can explicitly bind it to NULL using sqlite3_bind_null()
> .
>
> Do not confuse NULL, which is the NULL value, with 'NULL' in those quotes,
> which is a four character string.
>
> Simon.
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
