Re: automatically calculating ID field on INSERT
Date: 2000/06/24
Message-ID: <8j2e0k$qld$1_at_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 CEST