Dear all, Please can any e-mail address that ends "@sentec.co.uk" be removed from the mailing list as I am receiving a number of e-mails for ex-employees but there is no way to unsubscribe on the e-mails.
Many thanks With kind regards Liz -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Maks Verver Sent: 28 June 2017 08:50 To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] INSERT ... VALUES / want to "skip" default values 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 Liz Bond, Finance Controller Sentec Ltd phone: +44(0) 1223 303800 5 The Westbrook Centre fax: +44(0) 1223 303801 Milton Road Cambridge email: eb...@sentec.co.uk CB4 1YG, UK web: www.sentec.co.uk This email is confidential. If you have received it in error, please notify Sentec Ltd UK at postmas...@sentec.co.uk immediately, delete it from your system and note that you may not copy, distribute or use its contents. Sentec Limited is registered at the above address UK Company Number 3452194. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
