Sql-server – Oracle With Statement and Delete Together

oraclesql server

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?

Best Answer

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.

Related Question