CODEWITHSUNDEEP
oracle-databaseviewinline
ratsy
ratsy

Reputation: 605

inline view query

I wanted to delete some records which i added recently, from this table mytemp, please tell me what is wrong with this query, data in selected column had been populated using cursor 

DELETE FROM (SELECT ROWNUM RM, S from mytemp) where rm > 20;

error is:

ORA-01732: data manipulation operation not legal on this view

Upvotes: 1

Views: 946

Answers (2)

Mehmet Balioglu
Mehmet Balioglu

Reputation: 2302

It's the rownum>20 statement.

ROWNUM>x, where x values greater than a positive integer are always false.

select * from ANYTABLE where rownum>(ANY POSITIVE INTEGER)

doesn't return any record.

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

Check THIS for further info.

You can do the following:

delete from (select amount from TABLE t where t.amount=1000)

but it's the same as 

delete from TABLE where amount=1000

Upvotes: 1

Dan A.
Dan A.

Reputation: 2924

Edited for accuracy...

Here's the description of the error you are getting:

http://ora-01732.ora-code.com/

An attempt was made to use an UPDATE, INSERT, or DELETE statement on a view that contains expressions or functions or was derived from more than one table. If a join operation was used to create the view or the view contains virtual columns derived from functions or expressions, then the view may only be queried.

So it looks like an updateable view can be substituted for a table, as long as it doesn't join more than one table or use virtual columns. In your case, the problem is the virtual ROWNUM column.

Upvotes: 2

Related Questions