help Please !! oracle's Geekssss

[Hass Al]

Hello guys
i'm try to update a specific row lets say the 5th row How Can I do that i tried
UPDATE appointment SET status = 'Free' Where ROWNUM = 1;
it works Fine But other than the First Row I cant let say the 3rd row it wont work (0 rows updated.) even though i have record there !!!
I appreciated if u can reply before Soooooooooooon

[Jeanne Boyarsky]

Hass,
What is special about the third or fifth row? You can't rely on the ordering of the rows in the table as it can change.
You could introduce a key and use that to get your specific row.

[Mark Spritzler]

You will have to use a Subquery in your where clause to get the ID of the third record.
Say appointment has a primary key field called appointment_id
Example.

 
UPDATE appointment a
   SET a.status = 'Free' 
 WHERE a.appointment_ID = (SELECT b.appointment_id
                             FROM appointment b
                            WHERE ROWNUM = 3);
 

That should do the trick.
Mark

[SJ Adnams]

Mark,
you can't have a 'rownum' equal to 3, it will always be 1.
you would need to do something like
select c from (SELECT b.appointment_id c, rownum d FROM appointment b) where d = 3;

I agree with Jeanne, it's really poor style to do it like this (even if you put an 'order by' in somewhere. I would use a cursor for a task like this.
my 2c

[Mark Spritzler]

you can't have a 'rownum' equal to 3, it will always be 1.

How so? The subquery should "return all rows of Appointment, then filter out to the third row. Then in that case it will then actualy be the 1st row in the final result set. Anyway.
I don't know the reasoning for just getting the third row of something to update. I am sure too that there is a better design/solution for the actual goal.
Adding cursors or an order by, I think the problem is in the logic of why do you want to do this, more than poor style for a query. There is always many ways to write the same query. Some are faster than others, some take a long way around, but some work ok. I don't think a query like what I had written is poor style, if the reasoning behind needing such a query is sound.
Mark
Mark