I’m using oracle database and trying to delete duplicate records from that. For the same I’ve written a below query but getting this error
SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended"
Its giving red indicator under
outer keyword in
Left outer join in query.
DELETE FROM Duplicate LEFT OUTER JOIN ( SELECT MIN(RowId) as RWID, STUDENT_NAME, STUDENT_ROLLNO, STUDENT_SUBJECT FROM Duplicate GROUP BY STUDENT_NAME, STUDENT_ROLLNO, STUDENT_SUBJECT ) as KeepRows ON Duplicate.RowId = KeepRows.RWID WHERE KeepRows.RWID IS NULL;
Oracle does not permit
DELETE queries. This is clear if you look at the syntax diagram for
DELETE in the documentation.
There are many ways to rewrite this using a subquery in the
WHERE clause. The safest is probably the
NOT IN method proposed by Ankit because it works even when the columns are
rowid cannot be
I will point out that for a large number of records, it can be faster to empty the table and reload it:
CREATE TABLE nodups as SELECT d.* FROM duplicates d WHERE D.ROWID NOT IN (SELECT MIN(RowId) FROM Duplicate GROUP BY STUDENT_NAME, STUDENT_ROLLNO, STUDENT_SUBJECT); TRUNCATE TABLE duplicates; -- backup first! INSERT INTO duplicates SELECT * FROM nodups;