Re: [sqlite] Question about: Adding a record to a table with select failure
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
Re: [sqlite] Question about: Adding a record to a table with select failure
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
> On Behalf Of Jose Isaias Cabrera
> Sent: Monday, November 18, 2019 12:11 PM
> To: 'SQLite mailing list'
> 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
Re: [sqlite] Question about: Adding a record to a table with select failure
Peter da Silva, on Monday, November 18, 2019 08:07 PM, wrote... > > Assuming I'm understanding what the original message was about. > > Isn't this what BEGIN; INSERT OR IGNORE; UPDATE; COMMIT is the right tool for? The original message was about adding a new record using old values from an existing id, let's say 'p001'. Then, I found out that I needed to add that record if there was no 'p001' id. And yes, we were using INSERT to do this. UPDATE wouldn't work because it can not add a new record. So... josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about: Adding a record to a table with select failure
Thanks, Keith.
Keith Medcalf, on Monday, November 18, 2019 07:25 PM, wrote...
>
>
> On Monday, 18 November, 2019 15:01, Jose Isaias Cabrera, on
>
> >Keith Medcalf, on Monday, November 18, 2019 04:27 PM, wrote...
> >>
> >> This relies on two implementation details particular to SQLite3 which
> >> hold at present, but may of course change at any time:
> >> (1) that selecting a non-aggregate scalar column will return a value
> >> from (one of) the row(s) matching the value of the aggregate (most
> >> RDBMS used to do this, most now throw an error at this construct); and,
> >> (2) that the optimizer will not optimize "IfNull('p006', max(idate))"
> >> into 'p006' since the result must always be 'p006' which would of
> >> course render the select to be a simple select and not an aggregate
> >> causing "all hell to break loose".
>
> >Thanks Keith. So, you are saying that this is a bad INSERT, and I don't
> >know much to argue, but is working. If I take out the first IfNull, and
> >there is not, at least one instance of 'p006' in the table, the INSERT
> >never works. I was thinking of using COALESCE, but that would also mean
> >that one or the other would have to be not null. Any suggestion would be
> >appreciated.
>
> No, what I am saying is that this particular insert is working with the
> current version of SQLite3 and is unlikely to work with any other RDBMS
> that uses SQL as this is using an "implementation detail" specific to
> SQLite3 that does not exist elsewhere; and, secondly that the particular
> construction used putting the aggregate function in the second argument
> of a coalesce where the first argument is a constant is dependent on the
> query optimizer not "optimizing way" the entire expression based on the
> fact that the first argument is a not-null constant (though if this
> optimized away the aggregate-ness of the query this would be a bug in the
> optimizer, since the optimization process should not cause different
> results to occur).
>
> What I am saying is that you need to be aware of this and if you change
> versions of SQLite3 then you need to make sure that this query still
> operates as you intend it to operate.
>
> So far as I know the first constraint (the scalar use of columns that are
> not part of the group by clause in a query) is unlikely to change because
> this is a significant change to backwards compatibility and Richard is
> unlikely to make such a change without warnings in second coming type.
>
> The second is unlikely with SQLite3 because the optimizer is not based on
> a query re-write which would be more prone to this sort of issue.
>
> The second issue can be bypassed entirely by using bound parameters (since
> there is no way except by examining the bound parameter at execution time
> to know that a bound parameter is not NULL) thus precluding the
> possibility of optimizing away the aggregate function. As in:
>
> select ifnull(:a, max(idate)),
>ifnull(b, 1),
>ifnull(c, 2),
>:y,
>ifnull(e, 4),
>:idate
> from t
> where a == :a
>
> and binding values for :a, :y and :idate. There is no way to know at prepare
> time that parameter :a cannot be null so therefore the ifnull(:a... and the
> evaluation of the aggregate cannot be optimized away.
>
> >[clip]
> >
> >> >SELECT IfNull('p006', Max(idate)),
> >> > IfNull(b, 1),
> >> > IfNull(c, 2),
> >> > 'y',
> >> > IfNull(e, 4),
> >> > '2019-20-12'
> >> > FROM t
> >> > WHERE a = 'p006';
> >> >
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about: Adding a record to a table with select failure
Simon Slavin, on Monday, November 18, 2019 05:14 PM, wrote... > Being completely serious, whenever I see "undocumented" or "implementation > dependent" or > "optimization side-effect", or a SQL statement I can't parse in my head, I > usually decide > to do it in my programming language instead. This simplifies testing and > debugging, and > makes things easier for the poor engineer who has to understand my code. Thanks for this. Yes, I have lots of those. Some of these, I can probably ask the GURUs in this list, and they would come up with some beautiful, SQL, but then, I wouldn't understand how to support it. So, I do follow the statement above, as much as possible. > You can do clever things in a language like SQL which allows recursive > construction > clauses. But what strikes me as ingenious when I'm writing it can look > bizarre and > impenetrable to me, or someone else, a year later. Agreed. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about: Adding a record to a table with select failure
Assuming I'm understanding what the original message was about. Isn't this what BEGIN; INSERT OR IGNORE; UPDATE; COMMIT is the right tool for? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about: Adding a record to a table with select failure
On Monday, 18 November, 2019 15:01, Jose Isaias Cabrera
wrote:
>Keith Medcalf, on Monday, November 18, 2019 04:27 PM, wrote...
>>
>> This relies on two implementation details particular to SQLite3 which
>> hold at present, but may of course change at any time:
>> (1) that selecting a non-aggregate scalar column will return a value
>> from (one of) the row(s) matching the value of the aggregate (most
>> RDBMS used to do this, most now throw an error at this construct); and,
>> (2) that the optimizer will not optimize "IfNull('p006', max(idate))"
>> into 'p006' since the result must always be 'p006' which would of
>> course render the select to be a simple select and not an aggregate
>> causing "all hell to break loose".
>Thanks Keith. So, you are saying that this is a bad INSERT, and I don't
>know much to argue, but is working. If I take out the first IfNull, and
>there is not, at least one instance of 'p006' in the table, the INSERT
>never works. I was thinking of using COALESCE, but that would also mean
>that one or the other would have to be not null. Any suggestion would be
>appreciated.
No, what I am saying is that this particular insert is working with the current
version of SQLite3 and is unlikely to work with any other RDBMS that uses SQL
as this is using an "implementation detail" specific to SQLite3 that does not
exist elsewhere; and, secondly that the particular construction used putting
the aggregate function in the second argument of a coalesce where the first
argument is a constant is dependent on the query optimizer not "optimizing way"
the entire expression based on the fact that the first argument is a not-null
constant (though if this optimized away the aggregate-ness of the query this
would be a bug in the optimizer, since the optimization process should not
cause different results to occur).
What I am saying is that you need to be aware of this and if you change
versions of SQLite3 then you need to make sure that this query still operates
as you intend it to operate.
So far as I know the first constraint (the scalar use of columns that are not
part of the group by clause in a query) is unlikely to change because this is a
significant change to backwards compatibility and Richard is unlikely to make
such a change without warnings in second coming type.
The second is unlikely with SQLite3 because the optimizer is not based on a
query re-write which would be more prone to this sort of issue.
The second issue can be bypassed entirely by using bound parameters (since
there is no way except by examining the bound parameter at execution time to
know that a bound parameter is not NULL) thus precluding the possibility of
optimizing away the aggregate function. As in:
select ifnull(:a, max(idate)),
ifnull(b, 1),
ifnull(c, 2),
:y,
ifnull(e, 4),
:idate
from t
where a == :a
and binding values for :a, :y and :idate. There is no way to know at prepare
time that parameter :a cannot be null so therefore the ifnull(:a... and the
evaluation of the aggregate cannot be optimized away.
>[clip]
>
>> >SELECT IfNull('p006', Max(idate)),
>> > IfNull(b, 1),
>> > IfNull(c, 2),
>> > 'y',
>> > IfNull(e, 4),
>> > '2019-20-12'
>> > FROM t
>> > WHERE a = 'p006';
>> >
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about: Adding a record to a table with select failure
Not sure this helps, a way to a conditionally insert based on if record already exists, is a select with literals left outer joined to the maybe record and use a where test value is null. Something like this pseudo SQL insert into T (valueA, valueB') (select 'ValueA', 'ValueB' left outer join T where T.valueA ='valueA' and T.valueA is null); On 11/18/2019 2:14 PM, Simon Slavin wrote: > On 18 Nov 2019, at 10:00pm, Jose Isaias Cabrera wrote: > >> Thanks Keith. So, you are saying that this is a bad INSERT, and I don't >> know much to argue, but is working. If I take out the first IfNull, and >> there is not, at least one instance of 'p006' in the table, the INSERT never >> works. I was thinking of using COALESCE, but that would also mean that one >> or the other would have to be not null. Any suggestion would be appreciated. > Being completely serious, whenever I see "undocumented" or "implementation > dependent" or "optimization side-effect", or a SQL statement I can't parse in > my head, I usually decide to do it in my programming language instead. This > simplifies testing and debugging, and makes things easier for the poor > engineer who has to understand my code. > > You can do clever things in a language like SQL which allows recursive > construction clauses. But what strikes me as ingenious when I'm writing it > can look bizarre and impenetrable to me, or someone else, a year later. > ___ > 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
Re: [sqlite] Question about: Adding a record to a table with select failure
On 18 Nov 2019, at 10:00pm, Jose Isaias Cabrera wrote: > Thanks Keith. So, you are saying that this is a bad INSERT, and I don't know > much to argue, but is working. If I take out the first IfNull, and there is > not, at least one instance of 'p006' in the table, the INSERT never works. I > was thinking of using COALESCE, but that would also mean that one or the > other would have to be not null. Any suggestion would be appreciated. Being completely serious, whenever I see "undocumented" or "implementation dependent" or "optimization side-effect", or a SQL statement I can't parse in my head, I usually decide to do it in my programming language instead. This simplifies testing and debugging, and makes things easier for the poor engineer who has to understand my code. You can do clever things in a language like SQL which allows recursive construction clauses. But what strikes me as ingenious when I'm writing it can look bizarre and impenetrable to me, or someone else, a year later. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about: Adding a record to a table with select failure
Keith Medcalf, on Monday, November 18, 2019 04:27 PM, wrote...
>
> This relies on two implementation details particular to SQLite3 which hold>
> at present,
> but may of course change at any time:
> (1) that selecting a non-aggregate scalar column will return a value from
> (one of) the
> row(s) matching the value of the aggregate (most RDBMS used to do this, most
> now throw
> an error at this construct); and,
> (2) that the optimizer will not optimize "IfNull('p006', max(idate))" into
> 'p006' since
> the result must always be 'p006' which would of course render the select to
> be a simple
> select and not an aggregate causing "all hell to break loose".
Thanks Keith. So, you are saying that this is a bad INSERT, and I don't know
much to argue, but is working. If I take out the first IfNull, and there is
not, at least one instance of 'p006' in the table, the INSERT never works. I
was thinking of using COALESCE, but that would also mean that one or the other
would have to be not null. Any suggestion would be appreciated.
[clip]
> >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, 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
Re: [sqlite] Question about: Adding a record to a table with select failure
No. This is an aggregate query that relies on the fact that SQLite3 will
choose the values from (one of) the row(s) containing the aggregate to satisfy
select scalars that are not aggregates. Consider the query:
select a, max(idate), b from t where a == 'p006';
This will return the maximum value of idate for all the rows in t where a ==
'p006', and the values of a and b from (the same) one of the rows on which this
maximum is found. If no rows matching the condition a == 'p006' then the
maximum is NULL and the values of a and b are also NULL since there is no row
from which the values may be taken.
So, this query will return the values b, c, e from the (one of the rows) with
the max(idate) from all the rows in t having a == 'p006', or NULL for those
values if no such row exists. If no such row exists then the IfNull function
will convert those NULL values into the given values. The first ifnull will
never actually be executed (since the first value is not null, the second,
max(idate), will never be used). However, since IfNull is a function, in this
case taking two arguments, all the arguments must be evaluated BEFORE the
function can be evaluated.
This relies on two implementation details particular to SQLite3 which hold at
present, but may of course change at any time:
(1) that selecting a non-aggregate scalar column will return a value from (one
of) the row(s) matching the value of the aggregate (most RDBMS used to do this,
most now throw an error at this construct); and,
(2) that the optimizer will not optimize "IfNull('p006', max(idate))" into
'p006' since the result must always be 'p006' which would of course render the
select to be a simple select and not an aggregate causing "all hell to break
loose".
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Doug
>Sent: Monday, 18 November, 2019 12:49
>To: 'SQLite mailing list'
>Subject: Re: [sqlite] Question about: Adding a record to a table with
>select failure
>
>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
>> On Behalf Of Jose Isaias Cabrera
>> Sent: Monday, November 18, 2019 12:11 PM
>> To: 'SQLite mailing list'
>> 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
>
Re: [sqlite] Question about: Adding a record to a table with select failure
Doug, on Monday, November 18, 2019 02:48 PM, wrote...
>
> 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?
Imagine this select:
SELECT 'p006',1, 2, 'y', 4, '2019-02-11';
The IfNull is bringing these values back if the 'p006' does not exists. Try
the above select in SQLite3 tool. You will get a record. Even without table.
Since the original question was how can I INSERT... this will check the table
for 'p006', if it exists, it will bring some of the data from that existing
record and insert a new one. Ihth.
josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about: Adding a record to a table with select failure
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
> On Behalf Of Jose Isaias Cabrera
> Sent: Monday, November 18, 2019 12:11 PM
> To: 'SQLite mailing list'
> 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
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
Re: [sqlite] Question about: Adding a record to a table with select failure
> -Original Message-
> From: sqlite-users
> On Behalf Of Jose Isaias Cabrera
> Sent: Saturday, November 16, 2019 10:43 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Question about: Adding a record to a table
> with select failure
>
>
> Jake Thaw, on Saturday, November 16, 2019 08:39 AM, wrote...
> >
> > One approach might be something like this:
> >
> > INSERT INTO t (a, b, c, d, e, idate)
> > SELECT 'p006',
> >Coalesce(b, 1),
> >Coalesce(c, 2),
> >'y',
> >Coalesce(e, 4),
> >'2019-20-12'
> > FROM (SELECT 1)
> > LEFT JOIN
> >(SELECT a, b, c, e FROM t WHERE a = 'p006' ORDER BY idate
> DESC LIMIT 1);
> >
> > A slightly more succinct (but not universal) way:
> > Note: see point 1 of
> >
> https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_n
> on_aggregate_result_columns_that_are_not_in_the_group_by_clause
> >
> > 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.
Doug
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about: Adding a record to a table with select failure
Jake Thaw, on Saturday, November 16, 2019 08:39 AM, wrote...
>
> One approach might be something like this:
>
> INSERT INTO t (a, b, c, d, e, idate)
> SELECT 'p006',
>Coalesce(b, 1),
>Coalesce(c, 2),
>'y',
>Coalesce(e, 4),
>'2019-20-12'
> FROM (SELECT 1)
> LEFT JOIN
>(SELECT a, b, c, e FROM t WHERE a = 'p006' ORDER BY idate DESC LIMIT
> 1);
>
> A slightly more succinct (but not universal) way:
> Note: see point 1 of
> https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause
>
> 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';
Thanks, Jake. I like this last one. I appreciate it. Thanks.
josé
> On Sat, Nov 16, 2019 at 8:04 AM Jose Isaias Cabrera, on
> >
> >
> > 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
Re: [sqlite] Question about: Adding a record to a table with select failure
One approach might be something like this:
INSERT INTO t (a, b, c, d, e, idate)
SELECT 'p006',
Coalesce(b, 1),
Coalesce(c, 2),
'y',
Coalesce(e, 4),
'2019-20-12'
FROM (SELECT 1)
LEFT JOIN
(SELECT a, b, c, e FROM t WHERE a = 'p006' ORDER BY idate DESC LIMIT 1);
A slightly more succinct (but not universal) way:
Note: see point 1 of
https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause
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';
On Sat, Nov 16, 2019 at 8:04 AM Jose Isaias Cabrera wrote:
>
>
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about: Adding a record to a table with select failure
On 15 Nov 2019, at 9:04pm, Jose Isaias Cabrera wrote: > 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 The thing after THEN must be an expression, not a list of expressions separated by commas. You could probably use another SELECT after THEN to retrieve the list of values, just as you have after ELSE. (Note I have not actually tried this to make sure it works.) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about: Adding a record to a table with select failure
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
Re: [sqlite] Question about: Adding a record to a table with select failure
Simon Slavin, on Friday, November 15, 2019 11:58 AM, wrote... > > On 15 Nov 2019, at 4:48pm, Jose Isaias Cabrera, on > > > It does not get inserted. > > The SELECT returns zero lines. Therefore zero lines get inserted. You might > like to try > one where the SELECT returns more than one line. Yes, I expected this. I was just trying it as a show-result type of deal. :-) josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about: Adding a record to a table with select failure
On 15 Nov 2019, at 4:48pm, Jose Isaias Cabrera wrote: > It does not get inserted. The SELECT returns zero lines. Therefore zero lines get inserted. You might like to try one where the SELECT returns more than one line. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about: Adding a record to a table with select failure
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?
It does not get inserted.
sqlite> drop table t;
sqlite> create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
sqlite> insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4,
'2019-02-11');
sqlite> insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4,
'2019-02-11');
sqlite> insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4,
'2019-02-11');
sqlite> insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4,
'2019-02-11');
sqlite> insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4,
'2019-02-11');
sqlite> 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
sqlite> insert into t (a, b, c, d, e, idate)
...> SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p999';
sqlite> 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
sqlite>
josé
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about: Adding a record to a table with select failure
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? > -Original Message- > From: sqlite-users > On Behalf Of Jose Isaias Cabrera > Sent: Friday, November 15, 2019 6:21 AM > To: SQLite mailing list > Subject: Re: [sqlite] Adding a record to a table with one value > change > > > Simon Slavin, on Thursday, November 14, 2019 06:48 PM, wrote... > > > > On 14 Nov 2019, at 10:27pm, Jake Thaw, on > > > > > Why not like this? > > > > > > insert into t (a, b, c, d, e, idate) > > > SELECT a, b, c, 'y', e, '2019-02-12' FROM t WHERE a = 'p001' > ORDER BY > > > idate desc limit 1; > > > > Dammit. I thought I had tried this, and received a syntax > error. Now I see that it was because I missed out a comma. > Thanks for the correction. > > That just lets you know that you are a human. ;-) > > > Good illustration of why responses should go to the list rather > than direct to the OP. > > Indeed. > ___ > 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
