On Tue, 16 Apr 1996, I wrote:
>I would like to sequentially number groups of rows in a table. This is for
>a conversion project where I have an existing key, say foo and want to have
>a new key (foo, seq_nbr) instead. It is clear how to do this with embedded
>C. Just select all the records having key foo and then do a bunch of updates.
>I'd like to know if there are any SQL tricks that allow this to be done
>entirely within the database server.
Assume that the new table is created thus
CREATE TABLE foo (
old_key INTEGER4 NOT NULL NOT DEFAULT,
key_ext INTEGER4 NOT NULL WITH DEFAULT, /* This is the extended Key */
);
COMMIT;
Here's a nifty solution due to Rob Benada:
CREATE PROCEDURE update_key_ext
(old_key = INTEGER4 NOT NULL,
tid = INTEGER4 NOT NULL ) =
DECLARE new_max = INTEGER;
BEGIN
SELECT :new_max=max(ifnull(key_ext,0))+1
FROM foo
WHERE old_key = :old_key;
UPDATE foo
SET key_ext = :new_max
WHERE tid = :tid;
END;
COMMIT;
CREATE RULE insert_foo_rule
AFTER INSERT ON foo
WHERE NEW.old_key IS NOT NULL
EXECUTE PROCEDURE update_key_ext(old_key = NEW.old_key,
tid = NEW.tid);
COMMIT;
INSERT INTO foo(old_key) VALUES (1);
INSERT INTO foo(old_key) VALUES (1);
INSERT INTO foo(old_key) VALUES (2);
INSERT INTO foo(old_key) VALUES (2);
INSERT INTO foo(old_key) VALUES (2);
INSERT INTO foo(old_key) VALUES (2);
INSERT INTO foo(old_key) VALUES (2);
COMMIT;
SELECT * FROM foo;
Output:
1> select * from foo
+-------------+-------------+
|old_key |key_ext |
+-------------+-------------+
| 1| 1|
| 1| 2|
| 2| 1|
| 2| 2|
| 2| 3|
| 2| 4|
| 2| 5|
+-------------+-------------+
(7 rows)
2> commit
End of Request
--
Jeffrey Horn (horn@cs.wisc.edu) |BELZER,BERNHARDT,BOETTCHER,DRAVIS,FETTER
PHONE:(608) 244-8420 |GAPINSKI,GAUGER,HARMS,HIRSCHINGER,HORNE
FAX: (608) 221-5008 |JUECH,KLAJBOR,KROIS,KRONING,LEMKE,RUNGE
http://www.cs.wisc.edu/~horn/horn.html |STOCK,TAUBERT,TRESKE,WILLMERT,ZILLMER
In article <3501B643.145F98D8@btinternet.com> you write:
> I need to be able to create a table with a sequential value in column.
>This table is to be populated from an external ascii file.
>Is there any easy way to do this ?
>
>
It is probably best to add the sequence number outside of Ingres whilst
data is in flat file format, then COPY it into a table.
In case this isn't what you're looking for, see below for some other
ideas.
HTH
Neil Warnock
/*-----------------------------------------------------------
/* Apertus Systems Limited - Technical Notes
/*
/* Filename : sequencenos.tip
/* Location : Ingres 6.4
/* Author : Neil Warnock (nfw)
/* Date : Thu May 22 15:38:59 BST 1997
/*
/* Description : Adding sequence numbers to a table
/*----------------------------------------------------------
(to check for gaps in a sequence number range see SEQUENCENOHOLES.TIP)
To bulk add sequence numbers to a table, there are several options:
1. select all the rows and update each row from within the select-loop.
This is nasty RAT processing and should be avoided unless the no of
rows selected will be very low (<10)
2. Use a non-readonly cursor and 'update where current of...'.
Again, this is nasty RAT and should be avoided.
3. COPY the table to a flat file, add the sequencenos using a COBOL or C
program then copy it back into Ingres.
4. In OpenIngres, select the table into a temp(in-memory) table, then
perform a select loop (or cursor) update against each row, then insert
the temporary table back into a real database table. This should not
be used on enormous tables, because it will use a lot of memory and/or
page out to disk anyway!
5. update set seq_no_column = tid;. This is very fast, unsupported
and will leave holes in your sequence number range.
6. UPDATE
Ingres Q & A
To William's Home Page
Email William