Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to use WITH statement and DELETE together. But I get ORA-00928 error. Basically my code is like following

WITH TO_DELETE
AS
(
SELECT TableKey
  FROM SOME_TABLE
  WHERE SomeConditions LIKE '%'
)
DELETE FROM TO_DELETE

-- SELECT * FROM TO_DELETE

This code works in SQL Server 2005+ but gives error in Oracle. Any idea how can I solve this problem?

Following workaround exists.

  • use temp table and drop it.
  • use following inner select construct.

    DELETE FROM ( SELECT TableKey FROM SOME_TABLE WHERE SomeConditions LIKE '%' )

What I wish to know is there any way using WITH statement?

share|improve this question

1 Answer 1

The Oracle syntax is DELETE FROM (query). The query can be a SELECT with subquery factoring:

delete from (
   with to_delete as (
      select * from a
   ) 
   select * from to_delete 
);

The above query works in 11gR2 but fails with ORA-01732 (non-updateable view) on an old 9iR2.

share|improve this answer
    
With this syntax , can I use more than one delete? –  Atilla Ozgur Sep 20 '12 at 6:58
2  
More than one delete? sorry I can't compile your question. –  Vincent Malgrat Sep 20 '12 at 7:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.