0

I used a query to delete duplicate rows in sybase.

It worked; However when I used similar query for practice on oracle and it did not work;

Please advise

Sybase query that worked:

--delete all the duplicate rdb_id where row_id  is greater than the min row_id
delete ratings_xref..xref_ratg_obj_id
from  ratings_xref..xref_ratg_obj_id a, ratings_xref..xref_ratg_obj_id b
where a.rdb_id = b.rdb_id
and a.row_id > b.row_id
go

However, the below code did not work in oracle :

delete dup6
from dup6 d6,dup6 d61
where d6.rowid>d61.rowid
and d6.i=d6i.i;

Same with update :

Below sybase code works :

update  ratings_xref..xref_ratg_obj_id
set a.last_chg_usr=suser_name(),
a.last_chg_dt=getdate()
from  ratings_xref..xref_ratg_obj_id a, ratings_xref..xref_ratg_obj_id b
where a.rdb_id = b.rdb_id
and a.row_id > b.row_id

Whereas the oracle code does not work 

update dupli
set bb.chg_dt=sysdate-360
from dupli bb,dupli b
where bb.i=b.i
and bb.rowid>b.rowid;

The error received while deleting using the oracle query was :

Error report -
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
Nigel
  • 47
  • 1
  • 2
  • 8
  • 1
    Possible duplicate of [Removing duplicate rows from table in Oracle](http://stackoverflow.com/questions/529098/removing-duplicate-rows-from-table-in-oracle) – OldProgrammer Feb 13 '16 at 18:50
  • 1
    Please edit your question and explain what you mean by "Did not work"? Did it fail to delete any rows, did it delete the desired delete rows, or was an error reported? Also - in Oracle ROWID is not a serial value, and comparing them as shown above will almost certainly not produce the result you expect. Thanks. – Bob Jarvis - Слава Україні Feb 13 '16 at 19:23

0 Answers0