Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: automatically calculating ID field on INSERT

Re: automatically calculating ID field on INSERT

From: Joe Celko <71062.1056_at_compuserve.com>
Date: 2000/06/24
Message-ID: <8j2e0k$qld$1@nnrp1.deja.com>#1/1

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

In SQL-92, your solution would look like this, with the assumption that the table HigestId has one row and it starts with a value in it:

BEGIN ATOMIC
INSERT INTO MyTable (id, a, b, c, ...)
VALUES ((SELECT id FROM HighestId), new_a, new_b, new_c, ..); UPDATE HighestId

   SET id = id + 1;
END; or you get fancy and write something like this:

UPDATE HighestId

   SET id = id + CAST (RANDOM(id) * 100 AS INTEGER);

--CELKO--
Joe Celko, SQL and Database Consultant
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Jun 24 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US