I have some values as below:
MYSTRING
--------
Dharshan
Ramalingam
Devid
I can select the specific row value through below query
select * from ( select mystring , rownum rn FROM teststring )
where rn = 2;
tell the way to delete this second row giving the row no and give me the brief explanation . I have tried as below but its not work....
delete from testring where rownum=2;
if you want to delete all rows except one(just for understanding how the rownum works):
or, more simpler:
Your query returns rows randomly without a specified order. A heap-organized table is a table with rows stored in no particular order. This is a standard Oracle table.
From Oracle documentation,
Your above query shows you do not understand how ROWNUM works.
Your query will never delete any rows.
A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:
Because
ROWNUM = 2
is not true for the first row,ROWNUM
does not advance to 2. Hence, noROWNUM
value ever gets to be greater than 1.How to correctly use ROWNUM:
As you wanted to select a row based on ROWNUM, you could do something like pagination:
It happens in three levels:
I have tried this Query and it's working fine.
Please suggest that can we improve it's performance. Any comment is appreciated.
Rownum is a pseudocolumn. The rownum is not assigned permanently to the row. It means you can't build your query based on this criteria.
You should instead delete your row with
delete from teststring where mystring = 'RAMALINGAM';