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 How to insert row with max + 1 in a column
Date Wed, 26 Sep 2012 00:08:53 GMT
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

Mime
View raw message