Sql syntax error while doing left outer Join in SQL

Question

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;

MySQL <> SQL Server <> Oracle; please only tag the RDBMS you are really using. Also, just a post of code doesn't make a question. Put the information we need in your question; along with what you've done to try and solve the problem. — Thom A
– Thom A ♦, Commented Jan 19, 2020 at 12:24

Barbaros Özhan · Accepted Answer · 2020-01-19 13:18:18Z

2

What I can guess is that you are trying to delete the duplicate records from the Duplicate table. Your syntax doesn't seem correct from Oracle perspective. You may try below query -

DELETE Duplicate D1
 WHERE D1.ROWID NOT IN (SELECT MIN(RowId)
                          FROM Duplicate
                         GROUP BY STUDENT_NAME, STUDENT_ROLLNO, STUDENT_SUBJECT)

edited Jan 19, 2020 at 13:18

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

answered Jan 19, 2020 at 12:33

Ankit Bajpai

13.6k4 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

FreshTransistor Over a year ago

Yes this one of the possible solution of problem, but what's wrong with my query ..?

FreshTransistor Over a year ago

Any suggestion ..?

Ankit Bajpai Over a year ago

Just run the query I posted. This is the correct Oracle supported syntax.

Gordon Linoff · Accepted Answer · 2020-01-19 13:34:15Z

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;

Collectives™ on Stack Overflow

Sql syntax error while doing left outer Join in SQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related