使用with clause删除不相关记录

Question

SQL> select * from trigger_test
  2  /COL1                 COL2                 COL3
-------------------- -------------------- --------------------
AS02                 900                  OO
AS09                 400                  X1
AS05                 300                  X2
AS03                 2                    O1SQL> with sus as
  2  (
  3  select * from trigger_test where col2>100
  4  )
  5  delete from sus where col2<900;
delete from sus where col2<900
*
第 5 行出现错误:
ORA-00928: 缺失 SELECT 关键字我仅仅是举一个例子，想问一下这个能否使用with取出最终的记录呢？

Answer 1

我的理解是with只是动态产生一个结果集，这个应该只能用于查询

Answer 2

我們可以使用   WITH   Clause   來定義一個   query   block，然後在   SELECT   statement   的其它地方來使用這個   query   block。如果在一個很複雜的   Query   裡，我們必須重複的使用某個   subquery，使用   WITH   Clause   可以降低   Query   的複雜度以及提高   performance。WITH   Clause   所讀出的資料會暫存在   User   的   temporary   tablespace   中。   
  SQL>   WITH   
      2     DEPT_COSTS   AS   (   SELECT   D.department_name,   SUM(E.salary)   AS   dept_total   
      3                                         FROM   EMPLOYEES   E,   DEPARTMENTS   D   
      4       WHERE   E.department_id   =   D.department_id   
      5     GROUP   BY   D.department_name),   
      6     AVG_COST       AS   (   SELECT   SUM(dept_total)/COUNT(*)   AS   dept_avg   
      7                                         FROM   DEPT_COSTS)   
      8     SELECT   *   
      9         FROM   DEPT_COSTS   
    10       WHERE   dept_total   >   (SELECT   dept_avg   FROM   AVG_COST)   
    11     ORDER   BY   department_name;   
    
  DEPARTMENT_NAME                                 DEPT_TOTAL   
  ------------------------------   ----------   
  Sales--                                                         304500   
  Shipping--                                                   156400

Answer 3

等价于：select * from trigger_test where col2>100  and col2<900

Answer 4

刚查了一下with clause使用：
http://blog.csdn.net/phpnewbie/archive/2007/05/31/1633523.aspx
其语法：
WITH 
subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name);WITH是用于查询结果集，不能用于DML操作
并做了测试！SQL>WITH a AS (Select t.order_no,t.customer_no,t.et_code FROM customer_order t )
SELECT a.order_no ,a.customer_no
FROM a;
SQL> ORDER_NO                                         CUSTOMER_NO
------------------------------------------------ ----------------------------------------
61001                                            3001270039
41006                                            1002060012SQL> WITH a AS (Select t.order_no,t.customer_no,t.et_code FROM customer_order t )
Delete From a Where a.order_no='61001';
ORA-00928: missing SELECT keywordSQL

Answer 5

楼上的说的不准确，blog没有看。
但是如果是10g及其以后的版本，WITH语句可以支持DML（当然说的是整个句子表现为DML的），
例如INSERT INTO B
    WITH V AS ( SELECT ...)
    SELECT V.* FROM V
虽然可以用于DML，但是通常都是作为SELECT字句的FROM 对象使用，而不能作为UPDATE,INSERT,DELETE的直接引用对象,例如LZ的情况就不能支持。

Answer 6

看到LS的回答，我很高兴，经过尝试，是非常正确的，尝试过程如下：create table zzw1(id number,score number);
insert into zzw1 values(1,100);
insert into zzw1 values(2,90);
insert into zzw1 values(3,80);
insert into zzw1 values(1,10);
insert into zzw1 values(2,70);commit;
create table zzw2(id number,heighth number);
insert into zzw2 values(1,50);
insert into zzw2 values(2,30);
insert into zzw2 values(3,20);
insert into zzw2 values(1,90);
insert into zzw2 values(2,40);commit;select * from zzw1;
/*
    ID SCORE
1 1 100
2 2 90
3 3 80
4 1 10
5 2 70
*/select * from zzw2;
/*
    ID HEIGHTH
1 1 50
2 2 30
3 3 20
4 1 90
5 2 40
*/create table zzw3(id number,score number,heighth number);select id,sum(score) score from zzw1 group by id;select id,sum(heighth) heighth from zzw2 group by id;with a as (select id,sum(score) score from zzw1 group by id),
b as (select id,sum(heighth) heighth from zzw2 group by id)
select a.id,a.score,b.heighth from a,b
where a.id=b.id;insert into zzw3
with a as (select id,sum(score) score from zzw1 group by id),
b as (select id,sum(heighth) heighth from zzw2 group by id)
select a.id,a.score,b.heighth from a,b
where a.id=b.id;commit;select * from zzw3;delete from zzw3
where (id,score,heighth) in (with a as (select id,sum(score) score from zzw1 group by id),
b as (select id,sum(heighth) heighth from zzw2 group by id)
select a.id,a.score,b.heighth from a,b
where a.id=b.id);commit;update zzw3 
set score=20
where (id,score,heighth) in (with a as (select id,sum(score) score from zzw1 group by id),
b as (select id,sum(heighth) heighth from zzw2 group by id)
select a.id,a.score,b.heighth from a,b
where a.id=b.id);commit;select * from zzw3;受到了启发，甚是感谢

Answer 7

with 好像只能用在 select子句中。