How to insert row with max + 1 in a column

[Al Eridani <al...@gmail.com>]

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

insert into abc (abc_id, xyz_id, ver)
values (default,1,(SELECT coalesce((SELECT 1+MAX(ver) from abc where
xyz_id=1), 1) from abc))

it inserts a row with ver = NULL, the next one with ver = 1 and then
it fails because the subquery returns multiple rows.
If I add "distinct", like

insert into abc (abc_id, xyz_id, ver)
values (default,1,(SELECT distinct coalesce((SELECT 1+MAX(ver) from
abc where xyz_id=1), 1) from abc))

I can insert multiple rows and 'ver' keeps increasing, but the first
row inserted always has ver = NULL.

Thanks for pointing me in the right direction.

-- 
Al

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

[Al Eridani <al...@gmail.com>]

On Wed, Sep 26, 2012 at 1:51 AM, Knut Anders Hatlen
<kn...@oracle.com> wrote:
> Al Eridani <al...@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

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

[Knut Anders Hatlen <kn...@oracle.com>]

Al Eridani <al...@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