在Oracle中动态删除 [英] Dynamic delete in Oracle

问题描述

我有一些静态数据，我将在union all的帮助下创建select语句，并将这些数据与DB表(部门)进行比较..在减号的帮助下，我将获得DB表中的额外记录并且我需要删除那些记录...需要将现有记录与静态数据同步，希望这可以清除要求.

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..

尝试删除下面查询中的选择查询的resultSet我不确定我的错误在哪里..出现SQL错误:ORA-01732:在此视图上的数据操作操作不合法 01732. 00000-此视图上的数据操纵操作不合法"

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"

在这里，我试图删除与66,77不匹配的部门的所有记录

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));

推荐答案

  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
                       )
                   )
         )

或

  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
         )

但是请确保您在UNION ALL记录中没有select null,null,null,null 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

这篇关于在Oracle中动态删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！