Hi, how can I use "ROWNUM" to delete a specific row? just ROWNUM only not ROW_NUMBER() or ROWID.
I can select specific row using ROWNUM but I cannot use it in DELETE or UPDATE operation.
Sample Query in Select Statement:
SELECT SERIAL_NUMBER, NAME FROM (SELECT SERIAL_NUMBER, NAME, ROWNUM AS RN FROM (SELECT SERIAL_NUMBER, NAME FROM TABLE_NAME)) WHERE RN = 2;
it will execute. but how about update or delete operation? Because if i replace the outermost as
"DELETE SERIAL_NUMBER, NAME FROM" then subquery. It throws error data manipulation operation not legal.
I tried executing your code, but yes you are correct, executing the Delete or Update on the above query,will get the error "data manipulation operation not legal on this view", below mentioned are the rules for the DML operations in Oracle views. If you try to violate these thumb rules you will get above error message.
Your issues answer is provided in point 1(a) and point 2(a), so you would need ROWID to delete data.
Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly