Any way to use UPDATE...SET Number=ROWNUM ordered by ORDER BY?

[vinczej]

Hi All!

My question seems to be hard, but I can't get answer to this.

I can put a unique continuously growing row number into a field like next:

update TABLE set NUMBER=rownum ;

Value Number
-----------------
9 1
7 2
2 3
5 4


This code puts the own rownum in the records not ordered. My question is: How can I use this way, if I would like to put the number ordered by Value, like this:

Value Number
-----------------
9 4
7 3
2 1
5 2

How can I do, that the UPDATE command shall put the Number ordered by Value?

Thanks forward!

 

[vinczej]

Hi, I have got idea from my colleagues:

update TABLE t set t.NUMBER = rownum
where EXISTS (
select 0
from (select TABLEID
from TABLE
order by VALUE
)
where t.TABLEID = TABLEID
)
;

Not a simple solution, but works!

(It is important and relevant to use the aliases properly! The embedded select may not use any alias by my experience!).

 

[SantaMufasa]

The embedded select may not use any alias by my experience

Can you offer an example of the alias you cannot/may not use?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[dkyrtata]

Mufasa

I think what Vinczej means is that the inner most SELECT cannot reference the table-name or its fields that are being UPDATEd. I have had that problem too. The reason becomes more obvious if you try to create a VIEW out of that SELECT. In doing so you would not be able to reference the table being UPDATEd. Its no different when the "view" is just a sub-query in the FROM clause.

The outer SELECT on the other hand is not taking the role of a "view". It is a sub-query of the UPDATE, and therefore can make reference to the table being UPDATEd.

 

[Dagon]

You just put the alias against the view e.g.

update TABLE t1 set t1.NUMBER = rownum
 where EXISTS (
     select 0
      from (select TABLEID 
             from TABLE
             order by VALUE 
           ) t2
      where t1.TABLEID = t2.TABLEID
              )

For Oracle-related work, contact me through Linked-In.

 

[dkyrtata]

I see what you are saying.

Yes, you must give the view an alias otherwise your reference to TABLEID is ambiguous (since there are two of them)

You can also give TABLEID from the "view" an alias to remove the ambiguity

update TABLE t1 set t1.NUMBER = rownum
 where EXISTS (
     select 0
      from (select TABLEID [COLOR=red] tableID_vw [/color]
             from TABLE
             order by VALUE
           )
      where t1.TABLEID =[COLOR=red] tableID_vw [/color]
      )

 

[SantaMufasa]

You can also give TABLEID from the "view" an alias to remove the ambiguity

update TABLE t1 set t1.NUMBER = rownum
 where EXISTS (
     select 0
      from (select TABLEID tableID_vw 
             from TABLE
             order by VALUE
           )
      where t1.TABLEID = tableID_vw 
      )

Actually, no, Dkyrtata...You must do it the way that Dagon illustrates -- with the alias assigned outside of the in-line view definition:

select dummy from (select * from dual tableID_vw) where tableID_VW.dummy = 'X';
select dummy from (select * from dual tableID_vw) where tableID_VW.dummy = 'X'
                                                        *
ERROR at line 1:
ORA-00904: "TABLEID_VW"."DUMMY": invalid identifier

select dummy from (select * from dual) tableID_VW where tableID_VW.dummy = 'X';

DUM
---
X

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Dagon]

I think what Dkyrtata meant was that you can use a column alias to give the column a different name to that of the corresponding column in the outer table. However, I would argue that it is much better to use table aliases - it means that anyone looking at the query will know where the column has come from without having to look at the definitions of all the tables and it will also be slightly more efficient because Oracle doesn't have to work out where the tableID_vw is coming from.

For Oracle-related work, contact me through Linked-In.

 

[dkyrtata]

Yes, Dagon that is what I meant and that is what my example shows. Perhaps I chose a poor name for the alias which makes it look like I created a table-alias instead.

I agree. It is better to keep the same field names (especially for long queries)for the reasons you mentioned.

 

[carp]

The only issue I can see with

update TABLE t set t.NUMBER = rownum
 where EXISTS (
     select 0
      from (select TABLEID 
             from TABLE
             order by VALUE 
           )
      where t.TABLEID = TABLEID
              )
;

is that if you do not have unique values in the VALUE column, you may have non-unique values in the NUMBER column as well. Depending on what you are trying to do, this may or may not be a problem for you. A slightly different approach would be:

UPDATE table
   SET value = (SELECT rn 
                  FROM (SELECT rownum AS rn, row_id 
                          FROM (SELECT rowid AS row_id 
                                  FROM table b 
                                 ORDER BY value) 
                        )
                 WHERE row_id = table.rowid);

This will give you unique NUMBER values even if you have non-unique VALUE values.

Also, to avoid syntax confusion, it would be better to use non-reserved words in your code. Finally, I'm not sure where TABLEID came from - could somebody explain this to me? Is this a new function that I had not heard of? It does not work on my database (10.2.0.4.0 on HP-UX 64-bit).

 

[Dagon]

I think (although I may be wrong) that vinczej's example was just pseudo-code with "TABLE" standing in for the real table name. I assume that TABLEID was meant to represent the primary key on the table.

Actually, looking at the "solution", I'm not convinced it's particularly reliable either. Using the order by with an exists clause seems a bit too indirect to me and it's not obvious that Oracle will always update the outer table in the order that rows are retrieved by the inner query. Where is the rownum coming from exactly ? It should be coming from the inner query (the one which actually has the order by) but I suspect here it's from the outer query, which may or may not be updated in the same order as rows retrieved by the inner query.

This is a good example of something which may work in one version of Oracle but could fall apart in the next. Carp's solution looks much more robust.


For Oracle-related work, contact me through Linked-In.

 

[carp]

Dagon -
Yes, I had assumed TABLE, NUMBER, etc were for illustrative purposes as well. But even so, it can lead to confusion. my_table, order_number, etc would seem a better approach. Along this line, TABLEID is also an improvement, but it would be helpful to identify what it is (particularly for the easily-befuddled reader whose coffee still has not kicked in!).