Is it possible to conditionally insert a record?

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Is it possible to conditionally insert a record?

Shane Dev
Hello,

sqlite> create table source_table(value);
sqlite> create table max_value(max_value);
sqlite> insert into max_value select max(value) from source_table;
sqlite> select * from table_max_value;
max_value

sqlite>


How can the maximum value of column source_table.value be inserted into
max_value only if there are records in source_table? (If source_table is
empty, nothing should be inserted into max_value, not even a NULL)
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to conditionally insert a record?

Nelson, Erik - 2
Can you use something along the lines of this?

insert into max_value select value as "value" from source_table order by value desc limit 1;

Shane Dev Sent: Wednesday, January 10, 2018 11:49 AM
To: SQLite mailing list <[hidden email]>
Subject: [sqlite] Is it possible to conditionally insert a record?

Hello,

sqlite> create table source_table(value);
sqlite> create table max_value(max_value);
sqlite> insert into max_value select max(value) from source_table;
sqlite> select * from table_max_value;
max_value

sqlite>


How can the maximum value of column source_table.value be inserted into
max_value only if there are records in source_table? (If source_table is
empty, nothing should be inserted into max_value, not even a NULL)

----------------------------------------------------------------------
This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended recipient, please delete this message.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to conditionally insert a record?

Dan Kennedy-4
In reply to this post by Shane Dev
On 01/10/2018 11:48 PM, Shane Dev wrote:

> Hello,
>
> sqlite> create table source_table(value);
> sqlite> create table max_value(max_value);
> sqlite> insert into max_value select max(value) from source_table;
> sqlite> select * from table_max_value;
> max_value
>
> sqlite>
>
>
> How can the maximum value of column source_table.value be inserted into
> max_value only if there are records in source_table? (If source_table is
> empty, nothing should be inserted into max_value, not even a NULL)

You could add a WHERE clause to your SELECT.

   INSERT INTO max_value SELECT max(value) FROM src WHERE EXISTS (SELECT
1 FROM src);

Or similar.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to conditionally insert a record?

Shane Dev
Hi Dan,

Your statement seems to insert a NULL into max_value

sqlite> delete from source_table;
sqlite> delete from max_value;
sqlite> INSERT INTO max_value SELECT max(value) FROM source_table WHERE
EXISTS (SELECT 1 FROM source_table);
sqlite> select * from max_value;
max_value

sqlite>

Erik Nelson's solution works -

sqlite> delete from max_value;
sqlite> delete from source_table;
sqlite> insert into max_value select value from source_table order by value
desc limit 1;
sqlite> select * from max_value;
sqlite> insert into source_table select 6;
sqlite> insert into max_value select value from source_table order by value
desc limit 1;
sqlite> select * from max_value;
max_value
6
sqlite>

According to https://www.sqlite.org/lang_aggfunc.html -

max(X)

The max() aggregate function returns the maximum value of all values in the
group. The maximum value is the value that would be returned last in an
ORDER BY on the same column. Aggregate max() returns NULL if and only if
there are no non-NULL values in the group.

despite that -

sqlite> delete from source_table;
sqlite> select * from source_table order by value;
sqlite> select max(value) from source_table;
max(value)

sqlite>

The behavior of SELECT max(X) from an empty table appears to contradict the
documentation, or have I misunderstood something?




On 10 January 2018 at 19:38, Dan Kennedy <[hidden email]> wrote:

> On 01/10/2018 11:48 PM, Shane Dev wrote:
>
>> Hello,
>>
>> sqlite> create table source_table(value);
>> sqlite> create table max_value(max_value);
>> sqlite> insert into max_value select max(value) from source_table;
>> sqlite> select * from table_max_value;
>> max_value
>>
>> sqlite>
>>
>>
>> How can the maximum value of column source_table.value be inserted into
>> max_value only if there are records in source_table? (If source_table is
>> empty, nothing should be inserted into max_value, not even a NULL)
>>
>
> You could add a WHERE clause to your SELECT.
>
>   INSERT INTO max_value SELECT max(value) FROM src WHERE EXISTS (SELECT 1
> FROM src);
>
> Or similar.
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to conditionally insert a record?

R Smith-2
In reply to this post by Shane Dev
Perhaps like this:

   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
version 2.0.2.4.
   -- Script Items: 4          Parameter Count: 0
   --
================================================================================================

create table source_table(value);

create table max_value(max_value NOT NULL);

insert OR IGNORE into max_value select max(value) from source_table;

select * from max_value;


   --   Script Stats: Total Script Execution Time:     0d 00h 00m and
00.031s



On 2018/01/10 6:48 PM, Shane Dev wrote:

> Hello,
>
> sqlite> create table source_table(value);
> sqlite> create table max_value(max_value);
> sqlite> insert into max_value select max(value) from source_table;
> sqlite> select * from table_max_value;
> max_value
>
> sqlite>
>
>
> How can the maximum value of column source_table.value be inserted into
> max_value only if there are records in source_table? (If source_table is
> empty, nothing should be inserted into max_value, not even a NULL)
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to conditionally insert a record?

Shane Dev
Hi Ryan,

Nice! I have never used IGNORE before.

Would you agree the documentation is wrong for the case of SELECT max(X)
FROM [an empty table or subquery]?

max(X)

The max() aggregate function returns the maximum value of all values in the
group. The maximum value is the value that would be returned last in an
ORDER BY on the same column. Aggregate max() returns NULL if and only if
there are no non-NULL values in the group.

https://www.sqlite.org/lang_aggfunc.html


On 10 January 2018 at 21:44, R Smith <[hidden email]> wrote:

> Perhaps like this:
>
>   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> version 2.0.2.4.
>   -- Script Items: 4          Parameter Count: 0
>   -- ============================================================
> ====================================
>
> create table source_table(value);
>
> create table max_value(max_value NOT NULL);
>
> insert OR IGNORE into max_value select max(value) from source_table;
>
> select * from max_value;
>
>
>   --   Script Stats: Total Script Execution Time:     0d 00h 00m and
> 00.031s
>
>
>
> On 2018/01/10 6:48 PM, Shane Dev wrote:
>
>> Hello,
>>
>> sqlite> create table source_table(value);
>> sqlite> create table max_value(max_value);
>> sqlite> insert into max_value select max(value) from source_table;
>> sqlite> select * from table_max_value;
>> max_value
>>
>> sqlite>
>>
>>
>> How can the maximum value of column source_table.value be inserted into
>> max_value only if there are records in source_table? (If source_table is
>> empty, nothing should be inserted into max_value, not even a NULL)
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to conditionally insert a record?

petern
Functions and aggregates have to return a scalar value or NULL.  Please
recall functions as a basic concept from early high school:
https://en.wikipedia.org/wiki/Function

The query below will never return a NULL max(value) row from the
empty_table table:

WITH empty_table(value) AS (SELECT 1 WHERE 0) SELECT * FROM (SELECT
max(value)max_value FROM empty_table) WHERE max_value NOT NULL;

Also, INSERTing zero rows is conditioned by having no rows in the source
SELECT like the following pattern:

INSERT INTO ...  SELECT ... FROM ... WHERE  <the condition here filters all
rows>

Shane.  I encourage you experiment on your own in the shell of SQLite to
improve your intuition about SQL.  Everything isn't a special case to be
learned by rote.   There are are a few general patterns that, once
mastered, do explain what to expect most of the time.
















On Wed, Jan 10, 2018 at 1:20 PM, Shane Dev <[hidden email]> wrote:

> Hi Ryan,
>
> Nice! I have never used IGNORE before.
>
> Would you agree the documentation is wrong for the case of SELECT max(X)
> FROM [an empty table or subquery]?
>
> max(X)
>
> The max() aggregate function returns the maximum value of all values in the
> group. The maximum value is the value that would be returned last in an
> ORDER BY on the same column. Aggregate max() returns NULL if and only if
> there are no non-NULL values in the group.
>
> https://www.sqlite.org/lang_aggfunc.html
>
>
> On 10 January 2018 at 21:44, R Smith <[hidden email]> wrote:
>
> > Perhaps like this:
> >
> >   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> > version 2.0.2.4.
> >   -- Script Items: 4          Parameter Count: 0
> >   -- ============================================================
> > ====================================
> >
> > create table source_table(value);
> >
> > create table max_value(max_value NOT NULL);
> >
> > insert OR IGNORE into max_value select max(value) from source_table;
> >
> > select * from max_value;
> >
> >
> >   --   Script Stats: Total Script Execution Time:     0d 00h 00m and
> > 00.031s
> >
> >
> >
> > On 2018/01/10 6:48 PM, Shane Dev wrote:
> >
> >> Hello,
> >>
> >> sqlite> create table source_table(value);
> >> sqlite> create table max_value(max_value);
> >> sqlite> insert into max_value select max(value) from source_table;
> >> sqlite> select * from table_max_value;
> >> max_value
> >>
> >> sqlite>
> >>
> >>
> >> How can the maximum value of column source_table.value be inserted into
> >> max_value only if there are records in source_table? (If source_table is
> >> empty, nothing should be inserted into max_value, not even a NULL)
> >> _______________________________________________
> >> sqlite-users mailing list
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to conditionally insert a record?

Dan Kennedy-4
In reply to this post by Shane Dev
On 01/11/2018 03:41 AM, Shane Dev wrote:
> Hi Dan,
>
> Your statement seems to insert a NULL into max_value

So it does. How about this then:

   INSERT INTO max_value SELECT max FROM (
     SELECT max(value) AS max FROM source_table
   ) WHERE EXISTS (SELECT 1 FROM source_table);

Dan.




>
> sqlite> delete from source_table;
> sqlite> delete from max_value;
> sqlite> INSERT INTO max_value SELECT max(value) FROM source_table WHERE
> EXISTS (SELECT 1 FROM source_table);
> sqlite> select * from max_value;
> max_value
>
> sqlite>
>
> Erik Nelson's solution works -
>
> sqlite> delete from max_value;
> sqlite> delete from source_table;
> sqlite> insert into max_value select value from source_table order by value
> desc limit 1;
> sqlite> select * from max_value;
> sqlite> insert into source_table select 6;
> sqlite> insert into max_value select value from source_table order by value
> desc limit 1;
> sqlite> select * from max_value;
> max_value
> 6
> sqlite>
>
> According to https://www.sqlite.org/lang_aggfunc.html -
>
> max(X)
>
> The max() aggregate function returns the maximum value of all values in the
> group. The maximum value is the value that would be returned last in an
> ORDER BY on the same column. Aggregate max() returns NULL if and only if
> there are no non-NULL values in the group.
>
> despite that -
>
> sqlite> delete from source_table;
> sqlite> select * from source_table order by value;
> sqlite> select max(value) from source_table;
> max(value)
>
> sqlite>
>
> The behavior of SELECT max(X) from an empty table appears to contradict the
> documentation, or have I misunderstood something?
>
>
>
>
> On 10 January 2018 at 19:38, Dan Kennedy <[hidden email]> wrote:
>
>> On 01/10/2018 11:48 PM, Shane Dev wrote:
>>
>>> Hello,
>>>
>>> sqlite> create table source_table(value);
>>> sqlite> create table max_value(max_value);
>>> sqlite> insert into max_value select max(value) from source_table;
>>> sqlite> select * from table_max_value;
>>> max_value
>>>
>>> sqlite>
>>>
>>>
>>> How can the maximum value of column source_table.value be inserted into
>>> max_value only if there are records in source_table? (If source_table is
>>> empty, nothing should be inserted into max_value, not even a NULL)
>>>
>> You could add a WHERE clause to your SELECT.
>>
>>    INSERT INTO max_value SELECT max(value) FROM src WHERE EXISTS (SELECT 1
>> FROM src);
>>
>> Or similar.
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to conditionally insert a record?

Shane Dev
In reply to this post by petern
Hi Peter,

Your solution quite simple and obvious in hindsight. Just to be clear - I
am using the sqlite3 shell exclusively at the moment and only I post
questions when I am stuck with a problem or observe behavior which appears
not to be documented. I appreciate the time spent by yourself and other
list members answering my questions.

On 11 January 2018 at 00:21, petern <[hidden email]> wrote:

> Functions and aggregates have to return a scalar value or NULL.  Please
> recall functions as a basic concept from early high school:
> https://en.wikipedia.org/wiki/Function
>
> The query below will never return a NULL max(value) row from the
> empty_table table:
>
> WITH empty_table(value) AS (SELECT 1 WHERE 0) SELECT * FROM (SELECT
> max(value)max_value FROM empty_table) WHERE max_value NOT NULL;
>
> Also, INSERTing zero rows is conditioned by having no rows in the source
> SELECT like the following pattern:
>
> INSERT INTO ...  SELECT ... FROM ... WHERE  <the condition here filters all
> rows>
>
> Shane.  I encourage you experiment on your own in the shell of SQLite to
> improve your intuition about SQL.  Everything isn't a special case to be
> learned by rote.   There are are a few general patterns that, once
> mastered, do explain what to expect most of the time.
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> On Wed, Jan 10, 2018 at 1:20 PM, Shane Dev <[hidden email]> wrote:
>
> > Hi Ryan,
> >
> > Nice! I have never used IGNORE before.
> >
> > Would you agree the documentation is wrong for the case of SELECT max(X)
> > FROM [an empty table or subquery]?
> >
> > max(X)
> >
> > The max() aggregate function returns the maximum value of all values in
> the
> > group. The maximum value is the value that would be returned last in an
> > ORDER BY on the same column. Aggregate max() returns NULL if and only if
> > there are no non-NULL values in the group.
> >
> > https://www.sqlite.org/lang_aggfunc.html
> >
> >
> > On 10 January 2018 at 21:44, R Smith <[hidden email]> wrote:
> >
> > > Perhaps like this:
> > >
> > >   -- SQLite version 3.20.1  [ Release: 2017-08-24 ]  on SQLitespeed
> > > version 2.0.2.4.
> > >   -- Script Items: 4          Parameter Count: 0
> > >   -- ============================================================
> > > ====================================
> > >
> > > create table source_table(value);
> > >
> > > create table max_value(max_value NOT NULL);
> > >
> > > insert OR IGNORE into max_value select max(value) from source_table;
> > >
> > > select * from max_value;
> > >
> > >
> > >   --   Script Stats: Total Script Execution Time:     0d 00h 00m and
> > > 00.031s
> > >
> > >
> > >
> > > On 2018/01/10 6:48 PM, Shane Dev wrote:
> > >
> > >> Hello,
> > >>
> > >> sqlite> create table source_table(value);
> > >> sqlite> create table max_value(max_value);
> > >> sqlite> insert into max_value select max(value) from source_table;
> > >> sqlite> select * from table_max_value;
> > >> max_value
> > >>
> > >> sqlite>
> > >>
> > >>
> > >> How can the maximum value of column source_table.value be inserted
> into
> > >> max_value only if there are records in source_table? (If source_table
> is
> > >> empty, nothing should be inserted into max_value, not even a NULL)
> > >> _______________________________________________
> > >> sqlite-users mailing list
> > >> [hidden email]
> > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >>
> > >
> > > _______________________________________________
> > > sqlite-users mailing list
> > > [hidden email]
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to conditionally insert a record?

Shane Dev
In reply to this post by Dan Kennedy-4
Thanks, that works

On 11 January 2018 at 06:40, Dan Kennedy <[hidden email]> wrote:

> On 01/11/2018 03:41 AM, Shane Dev wrote:
>
>> Hi Dan,
>>
>> Your statement seems to insert a NULL into max_value
>>
>
> So it does. How about this then:
>
>   INSERT INTO max_value SELECT max FROM (
>     SELECT max(value) AS max FROM source_table
>   ) WHERE EXISTS (SELECT 1 FROM source_table);
>
> Dan.
>
>
>
>
>
>> sqlite> delete from source_table;
>> sqlite> delete from max_value;
>> sqlite> INSERT INTO max_value SELECT max(value) FROM source_table WHERE
>> EXISTS (SELECT 1 FROM source_table);
>> sqlite> select * from max_value;
>> max_value
>>
>> sqlite>
>>
>> Erik Nelson's solution works -
>>
>> sqlite> delete from max_value;
>> sqlite> delete from source_table;
>> sqlite> insert into max_value select value from source_table order by
>> value
>> desc limit 1;
>> sqlite> select * from max_value;
>> sqlite> insert into source_table select 6;
>> sqlite> insert into max_value select value from source_table order by
>> value
>> desc limit 1;
>> sqlite> select * from max_value;
>> max_value
>> 6
>> sqlite>
>>
>> According to https://www.sqlite.org/lang_aggfunc.html -
>>
>> max(X)
>>
>> The max() aggregate function returns the maximum value of all values in
>> the
>> group. The maximum value is the value that would be returned last in an
>> ORDER BY on the same column. Aggregate max() returns NULL if and only if
>> there are no non-NULL values in the group.
>>
>> despite that -
>>
>> sqlite> delete from source_table;
>> sqlite> select * from source_table order by value;
>> sqlite> select max(value) from source_table;
>> max(value)
>>
>> sqlite>
>>
>> The behavior of SELECT max(X) from an empty table appears to contradict
>> the
>> documentation, or have I misunderstood something?
>>
>>
>>
>>
>> On 10 January 2018 at 19:38, Dan Kennedy <[hidden email]> wrote:
>>
>> On 01/10/2018 11:48 PM, Shane Dev wrote:
>>>
>>> Hello,
>>>>
>>>> sqlite> create table source_table(value);
>>>> sqlite> create table max_value(max_value);
>>>> sqlite> insert into max_value select max(value) from source_table;
>>>> sqlite> select * from table_max_value;
>>>> max_value
>>>>
>>>> sqlite>
>>>>
>>>>
>>>> How can the maximum value of column source_table.value be inserted into
>>>> max_value only if there are records in source_table? (If source_table is
>>>> empty, nothing should be inserted into max_value, not even a NULL)
>>>>
>>>> You could add a WHERE clause to your SELECT.
>>>
>>>    INSERT INTO max_value SELECT max(value) FROM src WHERE EXISTS (SELECT
>>> 1
>>> FROM src);
>>>
>>> Or similar.
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Is it possible to conditionally insert a record?

Dan Kennedy-4
On 01/11/2018 12:48 PM, Shane Dev wrote:
> Thanks, that works

Or, if you have a lot of data and an index on "value", this one might be
faster:

   INSERT INTO max_value SELECT value FROM source_value ORDER BY value
DESC LIMIT 1;




>
> On 11 January 2018 at 06:40, Dan Kennedy <[hidden email]> wrote:
>
>> On 01/11/2018 03:41 AM, Shane Dev wrote:
>>
>>> Hi Dan,
>>>
>>> Your statement seems to insert a NULL into max_value
>>>
>> So it does. How about this then:
>>
>>    INSERT INTO max_value SELECT max FROM (
>>      SELECT max(value) AS max FROM source_table
>>    ) WHERE EXISTS (SELECT 1 FROM source_table);
>>
>> Dan.
>>
>>
>>
>>
>>
>>> sqlite> delete from source_table;
>>> sqlite> delete from max_value;
>>> sqlite> INSERT INTO max_value SELECT max(value) FROM source_table WHERE
>>> EXISTS (SELECT 1 FROM source_table);
>>> sqlite> select * from max_value;
>>> max_value
>>>
>>> sqlite>
>>>
>>> Erik Nelson's solution works -
>>>
>>> sqlite> delete from max_value;
>>> sqlite> delete from source_table;
>>> sqlite> insert into max_value select value from source_table order by
>>> value
>>> desc limit 1;
>>> sqlite> select * from max_value;
>>> sqlite> insert into source_table select 6;
>>> sqlite> insert into max_value select value from source_table order by
>>> value
>>> desc limit 1;
>>> sqlite> select * from max_value;
>>> max_value
>>> 6
>>> sqlite>
>>>
>>> According to https://www.sqlite.org/lang_aggfunc.html -
>>>
>>> max(X)
>>>
>>> The max() aggregate function returns the maximum value of all values in
>>> the
>>> group. The maximum value is the value that would be returned last in an
>>> ORDER BY on the same column. Aggregate max() returns NULL if and only if
>>> there are no non-NULL values in the group.
>>>
>>> despite that -
>>>
>>> sqlite> delete from source_table;
>>> sqlite> select * from source_table order by value;
>>> sqlite> select max(value) from source_table;
>>> max(value)
>>>
>>> sqlite>
>>>
>>> The behavior of SELECT max(X) from an empty table appears to contradict
>>> the
>>> documentation, or have I misunderstood something?
>>>
>>>
>>>
>>>
>>> On 10 January 2018 at 19:38, Dan Kennedy <[hidden email]> wrote:
>>>
>>> On 01/10/2018 11:48 PM, Shane Dev wrote:
>>>> Hello,
>>>>> sqlite> create table source_table(value);
>>>>> sqlite> create table max_value(max_value);
>>>>> sqlite> insert into max_value select max(value) from source_table;
>>>>> sqlite> select * from table_max_value;
>>>>> max_value
>>>>>
>>>>> sqlite>
>>>>>
>>>>>
>>>>> How can the maximum value of column source_table.value be inserted into
>>>>> max_value only if there are records in source_table? (If source_table is
>>>>> empty, nothing should be inserted into max_value, not even a NULL)
>>>>>
>>>>> You could add a WHERE clause to your SELECT.
>>>>     INSERT INTO max_value SELECT max(value) FROM src WHERE EXISTS (SELECT
>>>> 1
>>>> FROM src);
>>>>
>>>> Or similar.
>>>>
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users