3

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?

7

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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