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

[Dan Kennedy]

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



___
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



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Shane Dev]

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 (
> 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  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
>>> 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
>>
>
>
> ___
> 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] Is it possible to conditionally insert a record?

[Shane Dev]

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  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   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  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  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
> > >> 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
> > >
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Dan Kennedy]

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



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] bind blob lifetime

[Dave Milter]

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 times are when the parameter is re-bound, or the statement finalized.
>

Sorry for misunderstanding. I did not do any experiments, I only read
documentation couple of times about "bind_blob",
and it was not clear when destructor will be called by sqlite.
Thank you!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  

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  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  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
> >> 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
> >
> ___
> 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] 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  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
>> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R Smith]

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
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] 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  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
> 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] Is it possible to conditionally insert a record?

[Dan Kennedy]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Shane Dev]

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 ti_readonly before insert on readonly begin select
raise(FAIL, 'inserts not allowed in table readonly'); end;

sqlite> insert into readonly select 4;
Error: inserts not allowed in table readonly

Defining an empty table where inserts are prohibited is more or less
pointless but its purpose is only to demonstrate the raise function.

However the following trigger definition with a concatenated error gives me
an error message -

sqlite> drop trigger ti_readonly;
sqlite> CREATE TRIGGER ti_readonly before insert on readonly begin select
raise(FAIL, 'Not allowed to insert '||new.num||' into table readonly'); end;
Error: near "||": syntax error
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users