 | |
How to UPDATE using ROWNUM and ORDER BY
comp.databases.oracle comp.databases.oracle
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
How to UPDATE using ROWNUM and ORDER BY -
08-10-2004
, 09:43 AM
#2
| |||
| |||
|
|
I am trying to do the following: EXEC SQL UPDATE MY TABLE SET COL1 = :newValue WHERE COL1 = 0 AND ROWNUM = 1 ORDER BY COL2; (index on COL1, COL2) Pro*C does not process the "ORDER BY" statement. How can I achieve the above Thanks Sandra |
What are you attempting to accomplish?
#3
| |||
| |||
|
|
I am trying to do the following: EXEC SQL UPDATE MY TABLE SET COL1 = :newValue WHERE COL1 = 0 AND ROWNUM = 1 ORDER BY COL2; (index on COL1, COL2) Pro*C does not process the "ORDER BY" statement. How can I achieve the above Thanks Sandra |
It appears that what you want to do is to update the row having the
lowest value for col2 where col1=0. You could use this:
UPDATE MY_TABLE
SET col1 = :newValue
WHERE col1 = 0
AND col2 =
(SELECT MIN(col2) FROM MY_TABLE WHERE col1 = 0);
Of course if the table is very large and there is no index on col2,
this could be slow. Another alternative is to use a PL/SQL block.
DECLARE
CURSOR c IS
SELECT col1 FROM my_table
WHERE col1 = 0
ORDER BY col2
FOR UPDATE OF col1;
r c%ROWTYPE;
BEGIN
OPEN c;
FETCH c INTO r;
IF c%FOUND THEN
UPDATE my_table SET col1 = :newValue
WHERE CURRENT OF c;
END IF;
CLOSE c;
END;
An advantage here is that if the min(col2) value is in multiple rows
with col1 = 0, the first example will update all of them while the
second example will only update one. No way to tell which one though.
Hope this helps
Ken Denny
#4
| |||
| |||
|
#5
| |||
| |||
|
|
I am trying to do the following: EXEC SQL UPDATE MY TABLE SET COL1 = :newValue WHERE COL1 = 0 AND ROWNUM = 1 ORDER BY COL2; (index on COL1, COL2) Pro*C does not process the "ORDER BY" statement. How can I achieve the above Thanks Sandra |
database. Why would you wish to do this.
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
#6
| |||
| |||
|
|
It appears that what you want to do is to update the row having the lowest value for col2 where col1=0. You could use this: UPDATE MY_TABLE SET col1 = :newValue WHERE col1 = 0 AND col2 = (SELECT MIN(col2) FROM MY_TABLE WHERE col1 = 0); |
Could I do something like this:
UPDATE MY_TABLE
SET col1 = :newValue
WHERE col2 =
(SELECT MIN(col2) FROM MY_TABLE WHERE ROWNUM = 1 AND col1 = 0);
If this is OK, I was wondering what are the performance implications
of moving the "WHERE" clause to the outer update statement (index is
on col2 as well), i.e.:
UPDATE MY_TABLE
SET col1 = :newValue
WHERE col1 = 0
AND col2 =
(SELECT MIN(col2) FROM MY_TABLE WHERE ROWNUM = 1);
Thanks
Sandra
#7
| |||
| |||
|
|
Thank you all for your help, I have one further question. It appears that what you want to do is to update the row having the lowest value for col2 where col1=0. You could use this: UPDATE MY_TABLE SET col1 = :newValue WHERE col1 = 0 AND col2 = (SELECT MIN(col2) FROM MY_TABLE WHERE col1 = 0); Yes, That is what I was trying do and only update a single row (in one statement). Could I do something like this: UPDATE MY_TABLE SET col1 = :newValue WHERE col2 = (SELECT MIN(col2) FROM MY_TABLE WHERE ROWNUM = 1 AND col1 = 0); If this is OK, I was wondering what are the performance implications of moving the "WHERE" clause to the outer update statement (index is on col2 as well), i.e.: UPDATE MY_TABLE SET col1 = :newValue WHERE col1 = 0 AND col2 = (SELECT MIN(col2) FROM MY_TABLE WHERE ROWNUM = 1); Thanks Sandra |
after retreiving one row. That row can be any row within your table -
due to the random retreival by Oracle (will probably be the first
physical row in the table, most of the time).
With that - returning only one row - why request the min(col2) - since
you only have one row ?
2- What would almost make more sense is moving the 'rownum=1'
condition to the outer SQL statement (update), but there again you are
selectively picking the first row, when you don't know the order that
multiple rows may be returned.
FYI - rownum is not a row id and can not be counted on to identify a
specific row, other then the position that the row is returned with
the return set.
Furthermore, if you don't specify an 'order by clause' the rows can be
in any order.
#8
| |||
| |||
|
|
Thank you all for your help, I have one further question. It appears that what you want to do is to update the row having the lowest value for col2 where col1=0. You could use this: UPDATE MY_TABLE SET col1 = :newValue WHERE col1 = 0 AND col2 = (SELECT MIN(col2) FROM MY_TABLE WHERE col1 = 0); Yes, That is what I was trying do and only update a single row (in one statement). Could I do something like this: UPDATE MY_TABLE SET col1 = :newValue WHERE col2 = (SELECT MIN(col2) FROM MY_TABLE WHERE ROWNUM = 1 AND col1 = 0); If this is OK, I was wondering what are the performance implications of moving the "WHERE" clause to the outer update statement (index is on col2 as well), i.e.: UPDATE MY_TABLE SET col1 = :newValue WHERE col1 = 0 AND col2 = (SELECT MIN(col2) FROM MY_TABLE WHERE ROWNUM = 1); Thanks Sandra |
UPDATE my_table
set col1 = :newValue
where rowid =
(select rowid from my_table
where col1 = 0
and col2 =
(select min(col2) from my_table
where col1 = 0)
and rownum = 1);
Ken
#9
| |||
| |||
|
#10
| |||
| |||
|
|
UPDATE MY_TABLE SET COL1 = :NEWVALUE WHERE ROWID = (SELECT ROWID FROM MY_TABLE WHERE COL1 = 0 AND ROWNUM = 1 ORDER BY COL2) |
Since rhe pseudo-column ROWNUM is assigned BEFORE any ORDER BY clause,
the above query does not do what you seem to think it does.
<rant>
And my standard question about use of ROWNUM applies:
WHY USE IT?
If you really have duplicate rows (all columns identical values), then
removing all but one seems to be better than faking differences in
rows based on the arbtrary order of retrieval. If they aren't really
duplicate rows, then use the other columns that aren't identical to
correctly sort out the ONE row you really want. (then get a proper
primary key created on that table ASAP)
But if you really must do something like this, what's wrong with a
little PL/SQL block? (and don't forget the FIRST_ROWS hint to the
optimizer)
</rant>
Sorry, but I just really dislike ROWNUM and it's abuses.
Ed
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.