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.
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;
Advertisement
Answer
Oracle does not permit JOIN
s in 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 NULL
and rowid
cannot be NULL
.
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;