PL/sql_EXISTS

最新推荐文章于 2024-01-16 11:01:53 发布

Datrilla

最新推荐文章于 2024-01-16 11:01:53 发布

阅读量759

点赞数

分类专栏： PL/sql

本文链接：https://blog.csdn.net/u014646950/article/details/52415300

版权

PL/sql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

--------------------------------          
DELETE XJYTABS TA WHERE EXISTS
   (  SELECT *FROM XJYTAB1   TB
            WHERE ((TA.EXPRESS_ID IS NOT NULL) 
                    AND (TA.EXPRESS_ID=TB.ID)
                    AND(TA.KEYWORD_ID IS NULL)  
                    AND (TA.BLACK_ID IS NULL) 
                  )
   ); 
-------------------------------
UPDATE XJYTABS TA set EXPRESS_ID=NULL 
               WHERE EXISTS
        (SELECT *FROM XJYTAB1   TB
				  WHERE ((TA.EXPRESS_ID IS NOT NULL) 
                 AND (TA.EXPRESS_ID=TB.ID)
                 AND(  (TA.KEYWORD_ID IS NOT NULL)  OR (TA.BLACK_ID IS NOT NULL) )
                 )
        );
--*****************************************
SQL> CREATE TABLE XJYTAB1(ID NUMBER,T DATE);

Table created

SQL> CREATE TABLE XJYTABS(KEYWORD_ID NUMBER,BLACK_ID NUMBER,EXPRESS_ID NUMBER);

Table created

SQL> INSERT INTO XJYTAB1(ID,T) VALUES(1,TO_DATE('20160708','YYYYMMDD'));

1 row inserted

SQL> INSERT INTO XJYTAB1(ID,T) VALUES(2,TO_DATE('20160709','YYYYMMDD'));

1 row inserted

SQL> INSERT INTO XJYTAB1(ID,T) VALUES(3,TO_DATE('20160709','YYYYMMDD'));

1 row inserted

SQL> INSERT INTO XJYTAB1(ID,T) VALUES(4,TO_DATE('20160709','YYYYMMDD'));

1 row inserted

SQL> INSERT INTO XJYTAB1(ID,T) VALUES(NULL,TO_DATE('20160709','YYYYMMDD'));

1 row inserted

SQL> INSERT INTO XJYTABS(KEYWORD_ID,BLACK_ID,EXPRESS_ID) VALUES(2,1,1);

1 row inserted

SQL> INSERT INTO XJYTABS(KEYWORD_ID,BLACK_ID,EXPRESS_ID) VALUES(2,NULL,2);

1 row inserted

SQL> INSERT INTO XJYTABS(KEYWORD_ID,BLACK_ID,EXPRESS_ID) VALUES(NULL,3,3);

1 row inserted

SQL> INSERT INTO XJYTABS(KEYWORD_ID,BLACK_ID,EXPRESS_ID) VALUES(NULL,NULL,4);

1 row inserted

SQL> SELECT *FROM XJYTAB1;

        ID T
---------- -----------
         1 2016/7/8
         2 2016/7/9
         3 2016/7/9
         4 2016/7/9
           2016/7/9

SQL> SELECT *FROM XJYTABS;

KEYWORD_ID   BLACK_ID EXPRESS_ID
---------- ---------- ----------
         2          1          1
         2                     2
                    3          3
                               4

SQL> UPDATE XJYTABS TA set EXPRESS_ID=NULL
  2                 WHERE EXISTS
  3          (SELECT *FROM XJYTAB1   TB
  4  				  WHERE ((TA.EXPRESS_ID IS NOT NULL)
  5                   AND (TA.EXPRESS_ID=TB.ID)
  6                   AND(  (TA.KEYWORD_ID IS NOT NULL)  OR (TA.BLACK_ID IS NOT NULL) )
  7                   )
  8          );

3 rows updated

SQL> SELECT *FROM XJYTAB1;

        ID T
---------- -----------
         1 2016/7/8
         2 2016/7/9
         3 2016/7/9
         4 2016/7/9
           2016/7/9

SQL> SELECT*FROM XJYTABS;

KEYWORD_ID   BLACK_ID EXPRESS_ID
---------- ---------- ----------
         2          1 
         2            
                    3 
                               4
SQL> DELETE XJYTABS TA WHERE EXISTS
  2     (  SELECT *FROM XJYTAB1   TB
  3              WHERE ((TA.EXPRESS_ID IS NOT NULL)
  4                      AND (TA.EXPRESS_ID=TB.ID)
  5                      AND(TA.KEYWORD_ID IS NULL)
  6                      AND (TA.BLACK_ID IS NULL)
  7                    )
  8     );

1 row deleted

SQL> select*from xjytab1;

        ID T
---------- -----------
         1 2016/7/8
         2 2016/7/9
         3 2016/7/9
         4 2016/7/9
           2016/7/9

SQL> select*from xjytabs
  2  ;

KEYWORD_ID   BLACK_ID EXPRESS_ID
---------- ---------- ----------
         2          1 
         2            
                    3