Doug, on Tuesday, November 19, 2019 10:47 AM, wrote...
>
> 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.
True.
> 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 ???
Hi Doug. This is more or less "a hack" to make the INSERT work when the id
does not exists in the table. Please take a look a both Keith's email regarding
this subject, and he has done a wonderful job explaining what is happening. I
would probably damage something trying to explain it. ;-) What I can tell you
is that I need this INSERT to always INSERT something. Either a new record
based on an already existing id ('p001') in the table, or a new record based on
a non-existing id ('p006') in the table. The IfNull works beautifully to allow
for this. Why it works with Max(idate) on the first select, I don't know, but,
if I take it out, it does not. Thanks.
> Doug
> > -----Original Message-----
> > From: sqlite-users, on
Jose Isaias Cabrera
> > Sent: Monday, November 18, 2019 12:11 PM
>
> > 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
