Jose, at least two things bothers me about part of your query:
IfNull('p006', Max(idate))
The first is that 'p006' is never null so the second part of the ifnull() will
never be used.
The second thing is that the result of this ifnull() is to set the value of the
"a" field. However, the domain of "a" is 'p001', 'p002',... It is not a date
which would be returned by Max(idate).
I know you are trying to use side effects, but I don't understand ???
Doug
> -----Original Message-----
> From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org>
> On Behalf Of Jose Isaias Cabrera
> Sent: Monday, November 18, 2019 12:11 PM
> To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Question about: Adding a record to a table
> with select failure
>
>
> Doug, on Monday, November 18, 2019 12:31 PM, wrote...
> Jose Isaias Cabrera
> [clip]
> > > >
> > > > 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.
>
> Well, it does...
> sqlite> create table t (a, b, c, d, e, idate, PRIMARY KEY(a,
> idate));
> sqlite> INSERT INTO t
> ...> SELECT IfNull('p001', Max(idate)),
> ...> IfNull(b, 1),
> ...> IfNull(c, 2),
> ...> IfNull(d,'n'),
> ...> IfNull(e, 4),
> ...> '2019-20-11'
> ...> FROM t
> ...> WHERE a = 'p001';
> sqlite> select * from t;
> p001|1|2|n|4|2019-20-11
> sqlite>
>
> And, since I put an uniqueness on a and idate, now these can not
> be repeated, so if I run the same command again,
> sqlite> INSERT INTO t
> ...> SELECT IfNull('p001', Max(idate)),
> ...> IfNull(b, 1),
> ...> IfNull(c, 2),
> ...> IfNull(d,'n'),
> ...> IfNull(e, 4),
> ...> '2019-02-11'
> ...> FROM t
> ...> WHERE a = 'p001';
> Error: UNIQUE constraint failed: t.a, t.idate
> sqlite>
>
> I do not get a repeated record for 'p001' and 2019-02-11; But if
> they are different,
> sqlite> INSERT INTO t
> ...> SELECT IfNull('p002', Max(idate)),
> ...> IfNull(b, 1),
> ...> IfNull(c, 2),
> ...> IfNull(d,'n'),
> ...> IfNull(e, 4),
> ...> '2019-02-11'
> ...> FROM t
> ...> WHERE a = 'p002';
> sqlite> select * from t;
> p001|1|2|n|4|2019-02-11
> p002|1|2|n|4|2019-02-11
>
> It'll work. 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
