The Magic Max at work, forcing the query to return at least one record.
-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Jose Isaias Cabrera
Gesendet: Montag, 18. November 2019 20:11
An: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] 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
___________________________________________
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
