Results 1 to 2 of 2

Thread: SQL Question

  1. #1
    Join Date
    Dec 2003
    Location
    Minneapolis
    Posts
    7

    Unanswered: SQL Question

    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.
    Last edited by jabel; 06-23-04 at 11:07. Reason: adding DB info

  2. #2
    Join Date
    Dec 2003
    Location
    Minneapolis
    Posts
    7
    No ideas or is my question poorly worded?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •