Using a select max(blah) as an input parameter This may be a naive question, but is it possible to use a select max(blah) to update a parameter in an input statement?
input a,b,c into table1 values (10, 20, (select max(c) where a=10) + 1) for example?
I tried this in postgresql and got the hint
insert into &quot;MeterData&quot;.data(nmi, intervalend, &quot;E1&quot;, version) values
('E1234', now(), 12.4, (select max(version) where nmi = 'E1234' and interalend = now()) + 1)

ERROR:  column &quot;version&quot; does not exist
LINE 2: ('E1234', now(), 12.4, (select max(version) where nmi = 'E12...
                                           ^
HINT:  There is a column named &quot;version&quot; in table &quot;data&quot;, but it cannot be referenced from this part of the query.
SQL state: 42703
Character: 105

I'm not even sure if this is possible. The idea is that if the entry already exists in the table, I need to add the new data and update the version by 1.
Hope this makes sense.
Thanks to the help here, I've got this working now IF there is a value in version to start with, however, if there is no value yet, it sets to null and so doesn't increment on subsequent calls. I've tried setting a default value on that column to 1 (thought that would have done it, but no), and tried a few different ways unsuccessfully to get the query something like
insert into &quot;MeterData&quot;.data(nmi, intervalend, &quot;E1&quot;, version) values
('E1234', '2021-02-08 00:00:00+11', 12.4, (select max(d.version) from &quot;MeterData&quot;.data d where d.nmi = 'E1234' and d.intervalend = '2021-02-08 00:00:00+11') into value, case value==null then 1, else value+1 end))

but that just ends badly.
Is there a way to do this, or do I need to write a function to handle this (or do it in some other code external to the db?
I have no idea why this question was closed.. what the??? How is this not a relevant question?
As the comments state, you forgot to include the FROM clause in your SELECT query. Try this:
insert into &quot;MeterData&quot;.data(nmi, intervalend, &quot;E1&quot;, version) values
('E1234', now(), 12.4, (select max(version) from MeterData where nmi = 'E1234' and interalend = now()) + 1)