db-derby-user mailing list archives

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

Thank you, Knut, that's what I was looking for.

-- 
Al

Mime
View raw message