Re: Sorting update?

From: <rtproffitt_at_my-deja.com>
Date: Tue, 21 Sep 1999 20:12:28 GMT
Message-ID: <7s8or0$t4i$1@nnrp1.deja.com>

Thorsten,

Remember that in relational theory, you never know nor can depend on the physical order of rows in a table, thus an update with a sorted order seem to be oxymoron.

However, if you must replace position with a new ordering, then here are TWO solutions, one is PL/SQL, the other is an UPDATE:

---

Procedural method

---

1. An inline select sorts by salary forming a sorted result.
2. Selecting Rownum of result gives new position.
3. Need to remember original ROWID.
4. Use new position and ROWID to update the original table.

  Cursor C is
    SELECT Rownum NEWPOS, OrgRowID
    FROM

       (SELECT DISTINCT SALARY,NAME,ROWID ORGROWID
       FROM EMP);

  vpos number;
  vrow ROWID;
Begin
Open C;
Loop
  Fetch C Into vpos,vrow;
  Exit when c%NOTFOUND;
  UPDATE EMP
    set POS = vpos
  where ROWID = vrow;
End Loop;
Close C;
-- commit [don't forget to commit results] End;
/

---

Update Method

---

1. An inline select with Distinct sorts by salary (innermost)
2. The next inline adds the rownum for new position.
3. The subselect uses the Rowid from original table to the original ROWID of the result table from the inlines to get the proper NEW POSITION.... for all rows in the original table.
4. I don't know about performance, slow I think if there are many rows. Since it is a correlated subselect, I think the inline tables would be regenerated with EACH row of the original table....[comments anyone?] Nevertheless, if it HAD to be an UPDATE statement, this would get the job done...

Update EMP a
  set a.POS =
   (Select NEWPOS
    From

      (Select rownum NEWPOS, OrgRowID
       FROM
         (Select Distinct Salary,Name, Rowid OrgRowId
          From EMP) c
       ) b

    Where a.ROWID = b.ORGROWID)

Robert Proffitt
Beckman Coulter
Brea California
RTProffitt AT beckman DOT com

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Tue Sep 21 1999 - 15:12:28 CDT