Re: How to insert row with max + 1 in a column

[Knut Anders Hatlen]

Al Eridani <al.erid...@gmail.com> writes:

> Hello, I'm trying to insert rows in a table where one of the columns
> 'ver' is a smallint and I want it populated with the next higher value
> of those meeting a condition.
>
> If I try
>
> insert into abc (abc_id, xyz_id, ver)
> values (default,1,(SELECT coalesce((SELECT 1+MAX(ver) from abc where
> xyz_id=1), 1)))
>
> it gives me a syntax error about EOF. If I try
>
> [...]

Hi Al,

I think this should work:

insert into abc (abc_id, xyz_id, ver) values (default, 1,
   (select coalesce(max(ver), 0) + 1 from abc where xyz_id = 1))

-- 
Knut Anders