关联更新

SQL> create table testa as select object_id,object_name,status from user_objects where rownum<101;

SQL> create table testb as select * from testa where rownum<11;

Table created.

SQL> update testa set status = (select status from testb where testa.object_id=testb.object_id)
where exists (select null from testb where testa.object_id=testb.object_id);

10 rows updated.

SQL> commit;

Commit complete.
SQL> select * from testb;
        20 ICOL$                                              VALID
        46 I_USER1                                            VALID
        28 CON$                                               VALID
        15 UNDO$                                              VALID
        29 C_COBJ#                                            VALID
         3 I_OBJ#                                             VALID
        25 PROXY_ROLE_DATA$                                   VALID
        41 I_IND1                                             VALID
        54 I_CDEF2                                            VALID
        40 I_OBJ5                                             VALID

10 rows selected.

SQL> update testb set status='INVALID';

10 rows updated.

SQL> commit;

Commit complete.

 

SQL> select * from testa where status='INVALID';
        20 ICOL$                                              INVALID
        46 I_USER1                                            INVALID
        28 CON$                                               INVALID
        15 UNDO$                                              INVALID
        29 C_COBJ#                                            INVALID
         3 I_OBJ#                                             INVALID
        25 PROXY_ROLE_DATA$                                   INVALID
        41 I_IND1                                             INVALID
        54 I_CDEF2                                            INVALID
        40 I_OBJ5                                             INVALID

10 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94384/viewspace-620444/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94384/viewspace-620444/