Thursday, February 21, 2013

Use Order By With Delete Statement

if you are using order by in sub query with delete statement like the below :

 delete from tAccountScoring where riskscoring_id=1 and id in
(select * from tAccountScoring where last_update <= sysdate-3 AND rownum < 3  AND ACC_ID = 251 over (order by last_update desc))
You will have an error :


Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:   
*Action:
The workaround to solve this issue is simple like the below :

delete from tAccountScoring
where riskscoring_id=1 and id in
(select ID  from (select * from tAccountScoring
where last_update <= sysdate-3 AND rownum < 3  AND ACC_ID = 251 order by last_update))

Thank you
Osama Mustafa

3 comments:

  1. What benefit is there for the delete, to have the list in sorted order?

    Does it run faster?

    ReplyDelete
    Replies
    1. Don hope you doing fine , actually No point at all , but it was challenge between developers team to tweak this and i provide them with solutions

      Delete
  2. there is a logical reason for the order by if you use it with "where rownum <= x" as is used here. No reason to use order by in a delete otherwise.

    ReplyDelete