Error message on insert

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Error message on insert

Tim Streater-3
I want to insert a new row in my table, and while doing so setting a column to one more than the maximum value of that column, thus:

   insert into filters (absid, filter_name, enabled, filter_order) values (null, 'Untitled filter', 0, max(filter_order)+1)

However I get "Error: no such column: filter_order”. I had a look at the syntax diagram for insert which would seem to permit the above.

I can do this in code anyway so it’s not a show stopper, but what have I done incorrectly? My IDE appears to use sqlite 3.14.1.

Thanks.


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

Re: Error message on insert

Keith Medcalf

insert into filters (absid, filter_name, enabled, filter_order)
values (null, 'Untitled filter', 0, coalesce((select max(filter_order) from filters)+1,1))

If you want the filter_order by filter_name then you would need:

insert into filters (absid, filter_name, enabled, filter_order)
values (null, 'Untitled filter', 0, coalesce((select max(filter_order) from filters where filter_name='Untitled filter')+1,1))

(you need the coalesce to make sure the initial value is 1, unless you have defined the column in the table with a default of 1)

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Tim Streater
> Sent: Monday, 19 June, 2017 04:47
> To: SQLite Users
> Subject: [sqlite] Error message on insert
>
> I want to insert a new row in my table, and while doing so setting a
> column to one more than the maximum value of that column, thus:
>
>    insert into filters (absid, filter_name, enabled, filter_order) values
> (null, 'Untitled filter', 0, max(filter_order)+1)
>
> However I get "Error: no such column: filter_order”. I had a look at the
> syntax diagram for insert which would seem to permit the above.
>
> I can do this in code anyway so it’s not a show stopper, but what have I
> done incorrectly? My IDE appears to use sqlite 3.14.1.
>
> Thanks.
>
>
> --
> Cheers  --  Tim
> _______________________________________________
> 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
|  
Report Content as Inappropriate

Re: Error message on insert

Chris Locke-3
In reply to this post by Tim Streater-3
insert into filters (absid, filter_name, enabled, filter_order) values
(null, 'Untitled filter', 0, ((select max(filter_order) from filters)+1)

On Mon, Jun 19, 2017 at 11:46 AM, Tim Streater <[hidden email]> wrote:

> I want to insert a new row in my table, and while doing so setting a
> column to one more than the maximum value of that column, thus:
>
>    insert into filters (absid, filter_name, enabled, filter_order) values
> (null, 'Untitled filter', 0, max(filter_order)+1)
>
> However I get "Error: no such column: filter_order”. I had a look at the
> syntax diagram for insert which would seem to permit the above.
>
> I can do this in code anyway so it’s not a show stopper, but what have I
> done incorrectly? My IDE appears to use sqlite 3.14.1.
>
> Thanks.
>
>
> --
> Cheers  --  Tim
> _______________________________________________
> 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
|  
Report Content as Inappropriate

Re: Error message on insert

Tim Streater-3
In reply to this post by Keith Medcalf
On 19 Jun 2017, at 12:2, Keith Medcalf <[hidden email]> wrote:

> insert into filters (absid, filter_name, enabled, filter_order)
> values (null, 'Untitled filter', 0, coalesce((select max(filter_order) from
> filters)+1,1))
>
> If you want the filter_order by filter_name then you would need:
>
> insert into filters (absid, filter_name, enabled, filter_order)
> values (null, 'Untitled filter', 0, coalesce((select max(filter_order) from
> filters where filter_name='Untitled filter')+1,1))
>
> (you need the coalesce to make sure the initial value is 1, unless you have
> defined the column in the table with a default of 1)
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

Thanks, Keith and Chris. I had actually tried a "select max(…", but without, it would seem, enough brackets.


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