Forum Stats

  • 3,767,865 Users
  • 2,252,726 Discussions
  • 7,874,367 Comments

Discussions

ORA-00933-while delete the records in the table

User_IAP38
User_IAP38 Member Posts: 1 Green Ribbon

Hi All

while execute the below query that thrown an error like -0rq-00933(-inner-join-ora-00933-sql-command-not-properly-ended)

can you please help how to handle this scenario


Delete from final_ord a

inner join final_old b

on(a.order_num=b.order_num and a.inv=b.inv)

where date_submit<(current_date-b.din_days);

Tagged:

Best Answers

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,470 Red Diamond
    Accepted Answer

    Perhaps...

    as a correlated delete...

    delete from final_ord
    where 1 = (select 1
               from   final_ord b
               where  b.order_num = final_ord.order_num
               and    b.inv = final_ord.inv
               and    b.din_days < final_ord.current_date - final_ord.date_submit
              )
    

    You could also use a MERGE statement with a delete on the when matched portion, though that's not always the best way.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,301 Gold Trophy
    Accepted Answer

    I'd have thought of:

    delete from final_ord
    where exists (select 1
               from   final_ord b
               where  b.order_num = final_ord.order_num
               and    b.inv = final_ord.inv
               and    b.din_days < final_ord.current_date - final_ord.date_submit
              );
    

    This 1=(select 1 from ...) is rather unfamiliar to me.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,470 Red Diamond

    BEDE, you are correct of course.

    It was "coding on the fly" as the OP obviously hasn't provided us with test data to actually test code against.

  • mathguy
    mathguy Member Posts: 10,144 Blue Diamond

    First: Your syntax is incorrect. You can delete "through" a join, but the syntax is something like

    delete from
    (select ... from some_table a join some_table b on .... where .... )
    

    Second: Deleting "through" a join is logically meaningless if the relationship is many-to-many. Suppose one row from your table matches six other rows (same order_num and same inv ). Some of the matching rows satisfy the condition in your where clause and some don't. So, what is the task then: delete that row, or don't delete it? This is ambiguous, and computers can't clear up ambiguities for us.

    So, we must guess at your task. The other respondents so far guessed that a row from the table, which matches six other rows, should be deleted if at least one of the matching rows satisfies your filter. That is not the only reasonable guess; for example, your task could have been "delete only if all the matching rows satisfy the condition". There is no particular reason to believe "at least one" is right and "all" is wrong.

    But, in any case, this discussion shows why deleting through join in this case is logically incorrect, regardless of syntax (and, really, regardless of "programming" of any kind, in any language, etc.) You need to tell us what problem you are trying to solve.


    Third: If you must delete if at least one matching row satisfies your filter, the easiest way to write the statement is with an in condition. I also assume current_date is the customary system function, not a column in your table (like BluShadow assumed).

    delete from final_ord
    where  (order_num, inv) in
           (
             select order_num, inv
             from   final_ord
             where  date_submit < current_date - din_days
           )
    ;