CODEWITHSUNDEEP
sqloracle-databasesql-delete
promezhutok promezhutok
promezhutok promezhutok

Reputation: 1

ora-00933 sql command not properly ended delete statement

Getting the following error:

ora-00933 sql command not properly ended" error Oracle DELETE with JOIN and WHERE SQL statement.

I tried to conditionally delete records from Oracle DB table XEDOCS_LB based on 1st condition from INNER JOIN, and 2nd condition from WHERE as follows:

DELETE FROM XEDOCS_LB
INNER JOIN (SELECT ID 
            FROM XEFOLDERS_LB
            WHERE TRANS_TYPE = '80'
              AND TO_DATE(TRUNC(DATEOFUPDATE)) 
                    BETWEEN TO_DATE(SUBSTR('1220430',2,6),'YYMMDD') 
                        AND TO_DATE(SUBSTR('1231231',2,6),'YYMMDD')) XEFOLDERS 
        ON EFOLDERID = XEFOLDERS.ID
WHERE TO_DATE(TRUNC(DATEOFUPDATE)) <= TO_DATE(SUBSTR('1230518',2,6),'YYMMDD')

Upvotes: 0

Views: 96

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

That's invalid syntax in Oracle.

delete from xedocs_lb a
where exists (select null 
              from xefolders_lb b
              where b.trans_type = '80'
                and to_date(trunc(b.dateofupdate)) 
                              between to_date(substr('1220430',2,6),'YYMMDD') 
                                  and to_date(substr('1231231',2,6),'YYMMDD')
                and a.efolderid = b.id
             )
  and to_date(trunc(dateofupdate)) <= to_date(substr('1230518',2,6),'YYMMDD');

On the other hand, maybe you can make it simpler by

  • removing to_date call on dateofupdate column if its datatype is DATE - probably is, according to trunc and later comparison to date values
  • removing to_date(substr(...)) piece of code - why would you use that, if e.g. 1231231 is hardcoded string so you're extracting date from it? Use date literal (or to_date function with appropriate format model)

So:

delete from xedocs_lb a
where exists (select null 
              from xefolders_lb b
              where b.trans_type = '80'
                and trunc(b.dateofupdate) between date '2022-04-30' and date '2023-12-31' 
                and a.efolderid = b.id
             )
  and trunc(a.dateofupdate) <= date '2023-05-18';

Upvotes: 0

Related Questions