This discussion is archived
4 Replies Latest reply: Feb 8, 2012 8:33 AM by Frank Kulash RSS

update a column using a connect by or other for hierachical relationships

702401 Newbie
Currently Being Moderated
I have a column which represents the 'order' of which a record loaded out of a table.
This 'order' is coming in wrong.
I know, because of a relatoinship between two of the columns what the correct order should be.


So for example if I do this:

select transid, laborcode, supervisorfrom enclabor_iface
connect by prior laborcode = supervisor
start with supervisor = 0;


I get all the records in the correct order... but of course I'd like to figure out how to use this in an update statement to update the transid columns, which is the order.


Can someone tell me if this is possible, and if so, how to do so?


I have tried a few things with no luck yet, as the attempt took over 15 minutes to run so I thought I had coded it badly.


thanks

Jeff
  • 1. CONNECT BY and ROWNUM
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Jeff,

    Sure, that's possible.

    The ROWNUM pseudo-column is assigned as the CONNECT BY clause (including ORDER SIBLINGS BY) is being applied, so you can use ROWNUM to capture the hierarchical order.
    I don't have a copy of your table, so I'll use scott.emp to illustrate:
    SELECT       ename, empno, mgr
    ,       ROWNUM          AS r_num
    FROM       scott.emp
    START WITH     mgr     IS NULL
    CONNECT BY     mgr     = PRIOR EMPNO
    ORDER SIBLINGS BY     ename
    ;
    Output:
    ENAME      EMPNO   MGR      R_NUM
    ---------- ----- ----- ----------
    KING        7839                1
    BLAKE       7698  7839          2
    ALLEN       7499  7698          3
    JAMES       7900  7698          4
    MARTIN      7654  7698          5
    TURNER      7844  7698          6
    WARD        7521  7698          7
    CLARK       7782  7839          8
    MILLER      7934  7782          9
    JONES       7566  7839         10
    FORD        7902  7566         11
    SMITH       7369  7902         12
    SCOTT       7788  7566         13
    ADAMS       7876  7788         14
    The default CONNECT BY ordering guarantees that (for example) all of BLAKEs descendants will come after BLAKE and before anyone who is not a descendant of BLAKE. However, it says nothing about whether BLAKE will come before JONES, or vice-versa. If that's important, use ORDER SIBLINGS BY.

    To store those numbers in your table, do the query above in a MERGE statement.


     


    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
    If you're asking about a DML statement, such as MERGE, the sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.
    Explain, using specific examples, how you get those results from that data.
    Always say which version of Oracle you're using.

    Edited by: Frank Kulash on Feb 7, 2012 11:46 AM
  • 2. Re: CONNECT BY and ROWNUM
    702401 Newbie
    Currently Being Moderated
    so I need to use a merge statement as opposed to an update statement?
    could you show me that?
    thanks
    Jeff
  • 3. Re: CONNECT BY and ROWNUM
    sb92075 Guru
    Currently Being Moderated
    user11155958 wrote:
    so I need to use a merge statement as opposed to an update statement?
    could you show me that?
    thanks
    Jeff
    when all else fails, Read The Fine Manual

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9016.htm#SQLRF0160
  • 4. Re: CONNECT BY and ROWNUM
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Jeff,
    user11155958 wrote:
    so I need to use a merge statement as opposed to an update statement?
    With an UPDATE statement, you'd need to identify the correct ROWNUM for each individual row, one at a time. It's certainly opossible, but it's very inefficient. If ypu assign all the ROWNUMs at once, the coding is simpler as well as faster.
    could you show me that?
    Sure; I'm just waiting for the CREATE TABLE and INSERT statements for the original data (that is, with NULL transid; please include the PRIMARY KEY constraint), the desired results, and the Oracle version.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points