Oracle With Statement and Delete Together

Question

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?

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.

Answer 2

There are some restrictions in Oracle.You can use either of the Method to get your solution.

1. Use directly the table if possible.

DELETE SOME_TABLE
  WHERE SomeConditions LIKE '%';

2.Get rows which you don't want to delete in temporary table and you can delete all records from the original table and copy all records from temporary table or you can drop the original table and rename temporary table to original table.

CREATE TABLE TEMP_TABLE AS SELECT * FROM SOME_TABLE WHERE SomeConditions NOT LIKE '%';

DELETE FROM SOME_TABLE;

insert into SOME_TABLE select * from TEMP_TABLE;

OR

CREATE TABLE TEMP_TABLE AS SELECT * FROM SOME_TABLE WHERE SomeConditions NOT LIKE '%';

DROP TABLE SOME_TABLE;

ALTER TABLE TEMP_TABLE RENAME TO SOME_TABLE;

Several other solutions are also available for the problem.

Answer 3

Using an updateable view (whether an actual view or CTE) is a dangerous approach as it doesn't provide "blaring transparency" when it comes to what table(s) are going to have data deleted. Better to consider alternatives (such as have the WHERE predicate in your DELETE perform an "IN" subquery if there is suitable keying).