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_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';
Thanks, Jake. I like this last one. I appreciate it. Thanks.
josé
> On Sat, Nov 16, 2019 at 8:04 AM Jose Isaias Cabrera, on
> >
> >
> > 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
