Re: automatically calculating ID field on INSERT

Joe Celko wrote:
>
> >> 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.

Unfortunately, this makes the assumption that there has never been a higher id in the table than currently exists, which under some situations could be a mistake. Oracle has sequence numbers which automatically increment, although this feature is not necessarily available on other DBMS products. Without such a facility, this is a difficult problem to solve with a single SQL statement. If you can execute it within a transactional block of statements, then an alternative is to use an ID table which one updates (increment the id value) and extracts the new value before inserting the new row into MyTable. IE:

IdTable has 2 columns: tablename (char or varchar) and id (number or int)

	update IdTable set id = id + 1 where tablename = 'MyTable';
	insert into MyTable (id, a, b, c, ...)
		values ((select id from IdTable where tablename='MyTable'),
			new_a, new_b, newc, ...);
	commit;

William Boyle Received on Wed Jun 21 2000 - 00:00:00 CEST