UPDATE SET = (SELECT ) 語法的總結

這是3年前的一個總結了，最近發現提交的dba 這邊的這種sql 比較多，

雖然沒有出什麼錯，單還是有必要再拿出來提醒下大家，

update   a   set a1= (select  b1 from  b where a2=b2) 這樣的語法還是有很多限制的。

往下接著看吧




alter session  set  current_schema = netmis_app

upudate   nm_hardware   b  set   b.cacti_url = ( select   a.CACTI_FLUX_URL  from 
(select   nm_server_info.id     AS  ID ,nm_server_info.CACTI_FLUX_URL  AS CACTO_FLUX_URL 
 from  nm_hardware,nm_server_info
where nm_hardware.id = nm_server_info.id     
and nm_server_info.CACTI_FLUX_URL is not null) a   where   b.id=a.id)  


方法一。
--   用A  表的子段根據條件更新B表的相應資料。 
----------------------    要求 有主鍵做關聯。 


更新簡單檢視。 對於大資料量的更新，如果在巢狀查詢中使用 USE_HASN 提示，效率在下列方法中效率最好。 

update   ( select  a.cacti_url  ,b.cacti_flux_url  from  nm_hardware  a , nm_server_info  b 
where  a.id= b.id  and  b.cacti_flux_url is not null )  set  cacti_url= cacti_flux_url  



-----------------------------

方法二。 

update   nm_hardware   b  set   b.cacti_url = ( select   a.CACTI_FLUX_URL  from 
 nm_server_info a 
where b.id = a.id     
and a.CACTI_FLUX_URL is not null)
where  b.id= ( select  id  from  nm_server_info c   where  c.id = b.id and c.cacti_flux_url is not null )  
------------------------------------------
方法三。  
這個方法要求最嚴格， 要求主副表的返回行數要一致，並且關聯欄位要求是主鍵，且一一對應。
否則，會把目標表的其他行更新位NULL ，  但是適合條件會更新為正確的值。

update   nm_hardware   b  set   b.cacti_url = ( select   a.CACTI_FLUX_URL  from 
 nm_server_info a 
where b.id = a.id     
and a.CACTI_FLUX_URL is not null)




------------------------------------


方法四。 限定返回行。   此方法是方法三的改進，限制返回行。
update   nm_hardware   b  set   b.cacti_url = ( select   a.CACTI_FLUX_URL  from 
 nm_server_info a 
where b.id = a.id     
and a.CACTI_FLUX_URL is not null)
where exists ( select  id  from  nm_server_info c   where  c.id = b.id and c.cacti_flux_url is not null )  

------------------------------------------------

方法五。  

使用CURSOR 過程逐行更新。   

BEGIN  
FOR  ROW_REC  IN  (  
SELECT   A.ID  ,  B.CACTI_FLUX_URL  FROM   NM_HARDWARE  A  NM_SERVER_INFO  B  
WHERE    A.ID  =  B.ID  AND  B.CACTI_FLUX_URL  IS NOT  NULL )  
LOOP  
 UPDATE   NM_HARWARE   SET  CACTI_URL  = NVL(ROW_REC.CACTI_FLUX_INFO) 
 WHERE    ID =  ROW_REC.ID ; 
 
 END LOOP  
 COMMIT ; 
 END  ; 
 
---------------------------------------------
方法六 。採用ORACLE  的MERGE  語句 。此方法適用於 表B 是表 A 的 一個子集。 

    MERGE  INTO   TABLE_A   
    using  ( select  id  , b1  from  TABLE_B )  B 
    ON   (A.ID = B.ID ) 
    WHEN   MATCHED  THEN  UPDATE  SET A1=B.b1  
    WHEN   NOT  MATCHED   THEN  INSERT  INTO  (ID , A1) VALUES (NULL , NULL ) ;  
    
    
    注意這個地方,被插入了一個空值.因為 TABLE_B 的ID=9984在A中不能匹配,
    根本原因是ORACLE9必須有WHEN NOT MATCHED子句,
    但是ORACLE10可以不許要,也就是ORACLE10可以不寫WHEN NOT MATCHED ,
    就不必插入NULL值了,為解決這個問題,下一步會DELETE 表B 的ID=9984,
    這樣一來就不會執行WHEN NOT MATCHED 
    或者在A表中把NULL 值的行 清除。  




select  nm_hardware.id as aid, nm_hardware.cacti_url , nm_server_info.id     AS  ID ,nm_server_info.CACTI_FLUX_URL  AS CACTi_FLUX_URL 
 from  nm_hardware,nm_server_info
where nm_hardware.id = nm_server_info.id     
and nm_server_info.CACTI_FLUX_URL is not null
and  nm_hardware.id in ( 1169,1182,1183)


upudate   nm_hardware   b  set   b.cacti_url = ( select  a.CACTI_FLUX_URL  from 
   nm_server_info  a   where  a.id=b.id and  a.cacti_flux_url is not null )  
where  exists  ( select  '1'  from nm_hardware  c ,nm_server_info d where c."id"=d."id" and   c.cacti_flux_url is not null )





(select   nm_server_info.id     AS  ID ,nm_server_info.CACTI_FLUX_URL  AS CACTO_FLUX_URL 
 from  nm_hardware,nm_server_info
where nm_hardware.id = nm_server_info.id     
and nm_server_info.CACTI_FLUX_URL is not null) a   where   b.id=a.id) 


                                --lsliang