Skip to content
Advertisement

Sql syntax error while doing left outer Join in SQL

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 JOINs 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;
6 People found this is helpful
Advertisement