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] [EXTERNAL] bind blob lifetime

On Tue, Jan 9, 2018 at 7:28 PM, Hick Gunter wrote: > A bound blob or string is destroyed "after SQLite has finished with it". This > should be the case when sqlite3_clear_bindings() is called. Are you sure it > is not deleted then? Code reading suggests it should be. > > Other

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?

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 Subject: [sqlite] Is it possible to

[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

Re: [sqlite] Can error messages be concatenated in the raise function?

On 1/10/18, Shane Dev wrote: > Hello, > > From the documentation https://www.sqlite.org/syntax/raise-function.html, > it is not clear to me if the 'error-message' must be a fixed string. The current implementation requires that the 2nd argument to RAISE() be a fixed string.

[sqlite] Can error messages be concatenated in the raise function?

Hello, From the documentation https://www.sqlite.org/syntax/raise-function.html, it is not clear to me if the 'error-message' must be a fixed string. Is there a way to raise a concatenated message? For example, a fix string error message - CREATE TABLE readonly(num int); CREATE TRIGGER