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
