Re: automatically calculating ID field on INSERT

Hi Joe!

Joe Celko wrote:
>
> >> I have a table with a numeric ID field representing the primary key.
> How can I use standard SQL to insert a record so that always a new value
> is created for the field ID ? <<
>
> INSERT INTO MyTable (id, a, b, c, ...)
> VALUES ((SELECT MAX(id) FROM MyTable) + 1, new_a, new_b, new_c, ...);

I think that does not solve the OP's problem since AFAIK the select will result in NULL when the table is empty.

You have to use ISNULL or COALESCE or some simular function which returns the first non-null value or its parameter: INSERT INTO MY_TABLE
SELECT ISNULL(MAX(ID), 0) + 1,'VALUE1','VALUE2',... FROM MY_TABLE I'm not sure whether such a function exists in standard SQL and what it's called, but I guess that every DMBS has one.

Another solution to the problem might be using some auto increment datatype supplied by the DBMS (like identity in Sybase).

Regards,
Heinz Received on Wed Apr 19 2000 - 00:00:00 CEST