I'm really confused now. I don't understand the semantics of:
SELECT IfNull('p006', Max(idate)),
IfNull(b, 1),
IfNull(c, 2),
'y',
IfNull(e, 4),
'2019-20-12'
FROM t
WHERE a = 'p006';
versus this:
SELECT (a,b,c,d,e,idate) from t where a = "p006"
Doesn't the where clause that cannot be satisfied in both cases guarantee that
no rows will be selected, when there are no records in the database?
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
