UDB 8.1/AIX

We allow the users to enter information into one table, which the application uses to then create data in another table (they enter from and to)...

Something like this:

Table: Folio
FolioId From To Type BookNo FolioSeq
1 1 5 2 34 1
2 1 8 1 34 2
3 4 9 6 34 3
4 2 2 2 34 4
5 1 4 1 34 5

Would in turn create data like this in the BookDetail table...

Table: BookDetail

Id FolioId PageNo
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 2 1
7 2 2
8 2 3
9 2 4
10 2 5
11 2 6
12 2 7
13 2 8
14 3 4
15 3 5
16 3 6
17 3 7
18 3 8
19 3 9
20 4 2
21 5 1
22 5 2
23 5 3
24 5 4

I would like to issue an update statement to this table (after the user has finished entering data) for a particular BookId, update the PageNo with a page number, ordering from 1-N (number of pages). I have found that the row() over() function would give you the row number of a select statement, but how can I use that within my update statement?

So the PageNo are ordered 1-N in the order of FolioSeq, PageNo. The resulting update would look like the following (Note: The FolioId is not always in the same order as FolioSeq, in fact it is usually different):

Id FolioId PageNo
1 1 1
2 1 2
3 1 3
4 1 4
5 1 5
6 2 6
7 2 7
8 2 8
9 2 9
10 2 10
11 2 11
12 2 12
13 2 13
14 3 14
15 3 15
16 3 16
17 3 17
18 3 18
19 3 19
20 4 20
21 5 21
22 5 22
23 5 23
24 5 24

Any ideas how this could be accomplished?

I have tried (note that the columns are named a little differently, but you can probably see where each of them come from).

book_detail = bookdetail
physical_page = pageno
folio_item_id = folioid
folio_item = folio
els_number = bookno
folio_seq = folioseq

UPDATE bookdetail
SET pageno = rownumber() over()
WHERE folioid IN (SELECT folioid FROM els.folio WHERE bookno = '34' ORDER BY folioseq, pageno)
;

But this will not guarantee the order in which the pages are updated. It is critical that the ORDER BY clause be followed (i.e. folio_seq, physical_page)

Any ideas?

Thanks.

No ideas or is my question poorly worded?