Re: automatically calculating ID field on INSERT

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

You are right:

>> 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. <<

Here is the SQL-92 version:

INSERT INTO MyTable (id, a, b, c, ...)
VALUES (COALESCE(SELECT MAX(id) FROM MyTable) + 1, 1),

          new_a, new_b, new_c, ..);

--CELKO--
Joe Celko, SQL and Database Consultant

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Apr 19 2000 - 00:00:00 CEST