Re: [sqlite] Is it possible to conditionally insert a record?

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

Re: [sqlite] Is it possible to conditionally insert a record?

Thanks, that works On 11 January 2018 at 06:40, Dan Kennedy 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 ( >

Re: [sqlite] Is it possible to conditionally insert a record?

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

Re: [sqlite] Is it possible to conditionally insert a record?

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>

Re: [sqlite] Is it possible to conditionally insert a record?

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

Re: [sqlite] Is it possible to conditionally insert a record?

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

Re: [sqlite] Is it possible to conditionally insert a record?

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);

Re: [sqlite] Is it possible to conditionally insert a record?

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

Re: [sqlite] Is it possible to conditionally insert a record?

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

Re: [sqlite] Is it possible to conditionally insert a record?

] 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

[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