4 Replies Latest reply on Feb 8, 2012 4:33 PM by Frank Kulash

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

      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.


        • 1. CONNECT BY and ROWNUM
          Frank Kulash
          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
          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
          1 person found this helpful
          • 2. Re: CONNECT BY and ROWNUM
            so I need to use a merge statement as opposed to an update statement?
            could you show me that?
            • 3. Re: CONNECT BY and ROWNUM
              user11155958 wrote:
              so I need to use a merge statement as opposed to an update statement?
              could you show me that?
              when all else fails, Read The Fine Manual

              • 4. Re: CONNECT BY and ROWNUM
                Frank Kulash
                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.