oracle 多表联合更新

需要用到exists

 

UPDATE TBL_REALTIME_AD_USER_RPT T3
       SET T3.AD_PRICE = (SELECT PRICE
                            FROM TBL_ROTATE_AD T4
                           WHERE T3.AD_ID = T4.ID
                             AND TO_CHAR(T4.CREATE_DATE, 'YYYYMMDD') =
                                 V_REPORT_DATE)
     WHERE EXISTS
     (SELECT 1
              FROM TBL_ROTATE_AD T4
             WHERE T3.AD_ID = T4.ID
               AND TO_CHAR(T4.CREATE_DATE, 'YYYYMMDD') = V_REPORT_DATE)
       AND T3.REPORT_DATE = V_REPORT_DATE;

 

 

原贴：http://immortal2512.javaeye.com/blog/406175

来个例子：

目前有两个表 T1,T2

表：T1                                     表：T2

ID     NAME                           ID     NAME

1       aaaa                             2      aaaa

2       bbbb                             5      bbbb

3       cccc                             8      cccc

4       dddd                             7      dddd

 

现在根据T1和T2中的NAME列，T2表中ID列的值要改成T1表中的ID列中的值

语句如下：

UPDATE T2 a
       SET a.SID = (SELECT ID
                            FROM T1 b
                           WHERE a.NAME = b.NAME
                            )
     WHERE EXISTS
     (SELECT 1
              FROM T1 b
             WHERE a.Name = b.name
              ) ;

结果： 

表：T2

ID     NAME                       

1       aaaa  

2       bbbb     

3       cccc       

4       dddd