4

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?

0
9

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.

2
  • With this syntax , can I use more than one delete? Sep 20 '12 at 6:58
  • 4
    More than one delete? sorry I can't compile your question. Sep 20 '12 at 7:16
-1

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.

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.