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

Reply via email to