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)