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:sqlite-users-boun...@mailinglists.sqlite.org] > 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 > 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
