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_non_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'; On Sat, Nov 16, 2019 at 8:04 AM Jose Isaias Cabrera <jic...@outlook.com> wrote: > > > Doug, on Friday, November 15, 2019 11:42 AM, wrote... > > > > WRT Jose's original context, and just for my enlightment, what happens with > > the following: > > > > insert into t (a, b, c, d, e, idate) > > SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999'; > > > > where p999 does not define a record? Is a new record inserted with values > > of a,b,c, and e null? > > Ok, I promise that this will be the last email on this for me: > > I just came to my senses, and sometimes, I need to insert when the 'a' value > does not exists, as Doug just brought to my attention. So, I am trying to > insert a record with two new values using the last existing 'a'. If a does > not exists, then I need to add that record with the two values. I have been > trying a few INSERT with CASEs, but nothing is working. I know one of you > will make it look easy, but this is what I have done as of now: > create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate); > insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4, > '2019-02-11'); > insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4, > '2019-02-11'); > insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4, > '2019-02-11'); > insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4, > '2019-02-11'); > insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4, > '2019-02-11'); > select * from t; > 1|p001|1|2|n|4|2019-02-11 > 2|p002|2|2|n|4|2019-02-11 > 3|p003|3|2|n|4|2019-02-11 > 4|p004|4|2|y|4|2019-02-11 > 5|p005|5|2|y|4|2019-02-11 > > I have tried various combination of the following, > > insert into t (a, b, c, d, e, idate) VALUES > ( > CASE > SELECT a from t WHERE a = 'p006' idate desc limit 1 > WHEN a = NULL > THEN 'p006',1,2,'y',4,'2019-02-12' > ELSE SELECT a, b, c, 'y', e, '2019-20-12' from t WHERE a = 'p006' idate > desc limit 1 > END > ); > Error: near "SELECT": syntax error > sqlite> > > But, different syntax error have popped. Any help would be greatly > appreciated. Thanks. > > josé > _______________________________________________ > 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