Error while Updating or Deleting data using RowNum in Oracle 11g


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.


Asked by:- Aileereal
0
: 2657 At:- 12/10/2017 5:28:34 AM
ORACLE ORACLE 11G

i've been searching it for a week, and i can't even find a single sample query on the internet to do that in DELETE and UPDATE statement. 0
By : Aileereal - at :- 12/10/2017 5:33:30 AM


add comment to question



1 Answers
profileImage Answered by:- jaiprakash

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.

  1. To allow UPDATE on View rows
  • There should not be ROWNUM pseudo column in the view definition
  • There should not be any group by, Aggregate function in the view definition
  • There should not be DISTINCT key word in the view definition
  • There should not be any expressions as columns.
  1. To allow DELETE on View
  • There should not be ROWNUM pseudo column in the view definition
  • There should not be any group by, Aggregate function in the view definition
  • There should not be DISTINCT key word in the view definition
  1. To allow INSERT on a view
  • There should not be ROWNUM pseudo column in the view definition
  • There should not be any group by, Aggregate function in the view definition
  • There should not be DISTINCT key word in the view definition
  • There should not be any expressions as columns.
  • There should not be any NOT NULL columns in the base tables that are not selected in the view definition

Your issues answer is provided in point 1(a) and point 2(a), so you would need ROWID to delete data.

Source
2
At:- 12/10/2017 12:31:20 PM Updated at:- 12/10/2017 12:36:03 PM


comment to above answer

Patreon Become a Patron


Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use

Related Articles
13375
Connect to Oracle Database in C# (ASP.NET Web-Forms Example)

Subscribe Now

Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly


Related Questions