Hello,
Firstly, apologies for the genuine novice's novice question, but all
my trawling can't find an answer. Our skills are Java desktop RIA, and
we use Hibernate to do the majority of our SQL lifting - hence our
limitations in understanding what is probably basic SQL. Any pointers
as to where to find an answer would be most appreciated.
We have a "card" table (with typically a few thousand rows.) It has a
currently unused column "cardnum" (an integer) that was originally
supposed to have a sequence updating it, but for some unknown reason
was left null - and now we need to use it.
We can easily put a unique sequential value into each row thusly ...
CREATE SEQUENCE card_num_seq START 1;
UPDATE card SET cardnum=nextval('card_num_seq') WHERE card.cardnum IS NULL;
That is almost OK, but we would really like the sequence to be in
"creation order", and the above update is essentially random.
The card table does have a column "creationdate" (a timestamp), so we
can determine the order that the sequence needs to be.
Can anyone please point me towards the appropriate SQL syntax to add
the sequence to our card table in an appropriate card order?
Any pointers would be much appreciated!
Many thanks for your time,
-Damian