I want to delete duplicate rows from a table using rownum.
If I use this command
Delete from mytable where rownum > 1 (to delete all rows except rownum = 1) nothing is getting deleted.(There are 2 similar rows in mytable)
But If I use following command
Delete from mytable where rownum < 2 then row with rownum = 1 is getting deleted.
A bit confused why the first sql is not working while the second sql is working fine.
while using rownum in DML statement '>' is invalid, u have to use only '<' less than operator
Only this SQL query works
Delete from mytable where rownum < 2 then row with rownum = 1 is getting deleted
My confusion is if one operator '<' is working then why not '>'..??How come use of '>' is invalid whereas it is valid to use '<'.Can I get some more information/reding references for the same?
The rownum has one side effect. You cannot select a row number that is greater than a particular number. For example, if you want all but the first two rows and you execute the following statement:
SELECT ROWNUM, Empno, ename
WHERE ROWNUM > 2;
you receive the following message:
no rows selected
The reason is that when the first row is returned, it is given the ROWNUM value of 1. It is tested against the WHERE clause and then rejected. The next row is then given the ROWNUM value of 1. It is tested and also rejected. This continues until no more rows are in the table, and no rows are returned in the result set.
ROWNUM is a psuedocolumn , the value is generated when you select the records, therefore you cannot get values for the> [ greater than ]clause.
In relational database you cannot use rownum to eliminate duplicates..You should be using rowids...
Thanks for your help.The clarifications/suggestions are very useful to clear my doubts.
See this is how u can delete duplicate rows from the table:
To delete duplicate rows from this table, use the following query:
delete from empl a where rowid > (
select min(rowid) from empl b where
a.desc = b.desc and
a.emplid = b.emplid);