PDA

View Full Version : Sequence no's in tables

mwillems
09-30-02, 07:19
Hi,
Could someone help me out? I have table with a field that is called unique_id and I need to write some SQL that will populate this field for me with a sequence from 1 to x. I have tried using the tid but unfortunately ingres is very limited with it's use of the tid within SQL. Does anyone have any suggestions about how to do this?

Thanks,

Mark
rnealejr
09-30-02, 19:30
I am not familiar with ingres - but I believe you can do something like the following:

Lets say you have a field called UID - which is your sequential id.

Within your update statement do something like:

update table set uid = (select max(uid) from table) + 1 where ...
rnealejr
09-30-02, 19:32
If that was not helpful - check out the following link:

link (http://www.cs.mu.oz.au/~yuan/Ingres/seqnum.html)
mwillems
10-01-02, 04:50
Thanks for that, in the end I used the tid as the unique_id. I tried this originally but as part of a create table1 <table> as
select tid,fileds... from table2.

Tid wouldn't work with the create table statement but if I used the tid as part of an 'insert into ... select tid,fields.. from' then it seemed OK.

Thanks,

Mark
Damian Ibbotson
11-20-02, 12:32
It's been a while since I used Ingres but you can generate a sequential key with increments of 1 if you implement something along the lines of:

update yourtable
set unique_id = (
select count(*)
from yourtable a
where yourtable.tid >= a.tid)