Re: automatically calculating ID field on INSERT

In article <3950CFB9.869D8FC7_at_ieee.org>,   woboyle_at_ieee.org wrote:
> 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
> >
>
> 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
>
I am told DB2 also uses a generate function to provide a sequence number while MS SQL-Server provides an autonumber feature as a class of all things.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.