UPDATE SET = (SELECT ) 语法的总结

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)