> -----Original Message----- > From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> > On Behalf Of Jose Isaias Cabrera > Sent: Saturday, November 16, 2019 10:43 AM > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] Question about: Adding a record to a table > with select failure > > > Jake Thaw, on Saturday, November 16, 2019 08:39 AM, wrote... > > > > One approach might be something like this: > > > > INSERT INTO t (a, b, c, d, e, idate) > > SELECT 'p006', > > Coalesce(b, 1), > > Coalesce(c, 2), > > 'y', > > Coalesce(e, 4), > > '2019-20-12' > > FROM (SELECT 1) > > LEFT JOIN > > (SELECT a, b, c, e FROM t WHERE a = 'p006' ORDER BY idate > DESC LIMIT 1); > > > > A slightly more succinct (but not universal) way: > > Note: see point 1 of > > > https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_n > on_aggregate_result_columns_that_are_not_in_the_group_by_clause > > > > INSERT INTO t (a, b, c, d, e, idate) > > SELECT IfNull('p006', Max(idate)), > > IfNull(b, 1), > > IfNull(c, 2), > > 'y', > > IfNull(e, 4), > > '2019-20-12' > > FROM t > > WHERE a = 'p006';
I think that you will never insert the first record with a query like this, since the select returns 0 records of there are none in the database yet. Doug _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
