oracle ora-00933 sql命令未正确结束删除语句

yqkkidmi  于 2023-10-16  发布在  Oracle
关注(0)|答案(1)|浏览(138)

获取以下错误:
ora-00933 sql command not properly ended”错误Oracle删除了JOIN和WHERE SQL语句。
我尝试有条件地从Oracle DB表XEDOCS_LB中删除记录,基于来自INNER JOIN的第一个条件和来自WHERE的第二个条件,如下所示:

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')
cvxl0en2

cvxl0en21#

这在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');

另一方面,也许你可以让它更简单,

  • 删除dateofupdate列上的to_date调用如果其数据类型是DATE-根据trunc和后来与日期值的比较,可能是
  • 删除to_date(substr(...))段代码-为什么要使用它,如果例如1231231是硬编码的字符串,所以你要从中提取日期?使用date文字(或具有适当格式模型的to_date函数)

所以:

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';

相关问题