Dynamic delete in Oracle

Question

I have Some static data for that i am creating select statement with the help of union all and i am comparing those data with DB table (departments).. with the help of minus i will get extra records which are in DB table and i need to delete those record......Need to sync up existing records with static data i hope this will clear the requirement..

Trying to delete a resultSet of select query in below query i am not sure where is my mistake.. getting SQL Error: ORA-01732: data manipulation operation not legal on this view 01732. 00000 - "data manipulation operation not legal on this view"

Here i am trying to delete all the records from departments which are not matched with 66,77

  delete from (select department_id, department_name, manager_id,location_id from departments
     minus
      (select 66,'Administration',200,1700 from dual
    union all
    select 77,'Marketing',201,1800 from dual));

is it not as simple as `delete from departments where department_id not in (66,77)`? — Vamsi Prabhala, Dec 11 '16 at 15:13
@vkp thanks for the response i need to do with multiple operation with union all and minus — vinod, Dec 11 '16 at 15:16
Your question is not clear. Are you trying to delete from the table or from a result set of a previous query? If the latter, rewrite the query to return only the rows you want? — Dan Bracuk, Dec 11 '16 at 15:37
@Dan sorry for your confusion I am comparing both the query result.. This will give you the comparison result select department_id, department_name, manager_id,location_id from departments minus (select 66,'Administration',200,1700 from dual union all select 77,'Marketing',201,1800 from dual and i want to delete that — vinod, Dec 11 '16 at 15:39
Please explain exactly what you are trying to do, and in plain English. The requirement is not clear. "I am comparing" (in your last comment) is not a requirement. A requirement is something like this: I have a table (name) with these columns, and here are a few representative rows. (And do include a few representative rows.) I must delete rows from this table. (If in fact that is the assignment). Namely, I must delete the rows that satisfy the following conditions: (and state those conditions). Don't worry about MINUS and UNION - perhaps those are part of the solution, but not the REQUIREMENT — , Dec 11 '16 at 15:43
@MathGuy i have Some static data for that i am creating select statement with the help of union all so i am comparing those data with DB table (departments).. with the help of minus i will get extra records which are in DB table and i need to delete those record......Need to sync up existing records with static data i hope this will clear the requirement.. sorry for my english — vinod, Dec 11 '16 at 15:48

score 1 · Answer 1 · answered Dec 11 '16 at 15:15

1

deletes work on tables, not results of select statements. Here, you just want to delete all the records besides those with IDs 66 and 77:

DELETE FROM departments WHERE id NOT IN (66, 77)

answered Dec 11 '16 at 15:15

Mureinik

297,002
52
306
350

you mean to say where ( select * from tab) wont work...correct me if m wrong – vinod Dec 11 '16 at 15:20
@vinod yup. It may work on some edge case queries, but as a general rule of thumb, it doesn't. – Mureinik Dec 11 '16 at 15:21
i was referring this link http://stackoverflow.com/questions/17548751/how-to-write-a-sql-delete-statement-with-a-select-statement-in-the-where-clause thanks for your response – vinod Dec 11 '16 at 15:23
@vinod oh, my bad, having a query in the **where** clause is something completely different than what you tried to do in the OP. – Mureinik Dec 11 '16 at 15:25
okay so its confirmed we can not delete the resultset I will go with NOT IN approach – vinod Dec 11 '16 at 15:27
@mathguy i have added my comment kindly check – vinod Dec 11 '16 at 15:49

David דודו Markovitz · Accepted Answer · 2016-12-13T05:15:51.853

  delete departments

  where  department_id in
         (
            select department_id

            from  (    select  department_id, department_name, manager_id,location_id 
                       from    departments

                       minus

                       (          select 66,'Administration',200,1700 from dual
                       union all  select 77,'Marketing'     ,201,1800 from dual
                       )
                   )
         )

or

  delete departments

  where  (department_id, department_name, manager_id,location_id) not in
         (          select 66,'Administration',200,1700 from dual
         union all  select 77,'Marketing'     ,201,1800 from dual
         )

But make sure you don't have select null,null,null,null from dual among your UNION ALL records or nothing will be deleted

Ok thanks for clear one .. but in my case that won't be the case for null.. :) — vinod, Dec 13 '16 at 05:15

Dynamic delete in Oracle

2 Answers2

Linked