How to UPDATE using ROWNUM and ORDER BY

[Sandy]

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

[Turkbear]

Re: How to UPDATE using ROWNUM and ORDER BY

sandy_lucca@yah oo.com (Sandy) wrote:
[color=blue]
>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[/color]

Why are you using an ORDER BY or ROWNUM in an update statement?

What are you attempting to accomplish?

[Ken Denny]

Re: How to UPDATE using ROWNUM and ORDER BY

sandy_lucca@yah oo.com (Sandy) wrote in message news:<f2c5a2a1. 0408100643.841f 9eb@posting.goo gle.com>...[color=blue]
> 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[/color]

"order by" is not valid in an update command.
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

[Pratap]

Re: How to UPDATE using ROWNUM and ORDER BY

UPDATE MY_TABLE
SET COL1 = :newValue
WHERE COL1 = 0
and col2 = ( select min(col2) from my_table )

Should do it

Pratap

[Niall Litchfield]

Re: How to UPDATE using ROWNUM and ORDER BY

sandy_lucca@yah oo.com (Sandy) wrote in message news:<f2c5a2a1. 0408100643.841f 9eb@posting.goo gle.com>...[color=blue]
> 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[/color]

If the above worked you would be updating an unpredictable row in the
database. Why would you wish to do this.

Niall Litchfield
Oracle DBA

http://www.niall.litchfield.dial.pipex.com

[Sandy]

Re: How to UPDATE using ROWNUM and ORDER BY

Thank you all for your help, I have one further question.
[color=blue]
> 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);[/color]

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

[GQ]

Re: How to UPDATE using ROWNUM and ORDER BY

sandy_lucca@yah oo.com (Sandy) wrote in message news:<f2c5a2a1. 0408110648.31d2 0a0a@posting.go ogle.com>...[color=blue]
> Thank you all for your help, I have one further question.
>[color=green]
> > 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);[/color]
>
> 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[/color]

1- Rownum doesn't buy you anything, other then ending the inner select
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.

[Ken Denny]

Re: How to UPDATE using ROWNUM and ORDER BY

sandy_lucca@yah oo.com (Sandy) wrote in message news:<f2c5a2a1. 0408110648.31d2 0a0a@posting.go ogle.com>...[color=blue]
> Thank you all for your help, I have one further question.
>[color=green]
> > 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);[/color]
>
> 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[/color]

You need the "where col1 = 0" both places. This will work:

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

[Kevin]

Re: How to UPDATE using ROWNUM and ORDER BY

UPDATE MY_TABLE
SET COL1 = :NEWVALUE
WHERE ROWID =
(SELECT ROWID
FROM MY_TABLE
WHERE COL1 = 0
AND ROWNUM = 1
ORDER BY COL2)

[Ed prochak]

Re: How to UPDATE using ROWNUM and ORDER BY

kk2796@hotmail. com (Kevin) wrote in message news:<5a1dc659. 0408131058.78d2 362a@posting.go ogle.com>...[color=blue]
> UPDATE MY_TABLE
> SET COL1 = :NEWVALUE
> WHERE ROWID =
> (SELECT ROWID
> FROM MY_TABLE
> WHERE COL1 = 0
> AND ROWNUM = 1
> ORDER BY COL2)[/color]

Kevin,

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