db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: How to insert row with max + 1 in a column
Date Wed, 26 Sep 2012 08:51:35 GMT
Al Eridani <al.eridani@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

Mime
View raw message