# SQL 兩表比對 UPDATE 多筆
###### tags: `SQL` `MSSQL` `ORACLE`

oracle sql
方法一

beign  
  for rec in(select no2,name from t2) loop  
    update t1   
    set no1=rec.no2 
    where name=rec.name 
  end loop;    
end;  

方法二

update t1 a set a.no1=(select no2 from t2 b where a.name=b.name);

ms sql
在第一句update table的時候, 吃不到 table在子查
要包起來 再最外面給一個from

-- a_cnt
	UPDATE @G_F_FHRP01 
	SET a_cnt = (
		select count(1) cnt
		from ca100 a
		WHERE open_dt >=  @P_YYYY + '-' + @P_MM1 + '-01'
		and open_dt < DATEADD(MONTH, 1  , @P_YYYY + '-' +  @P_MM2 +'-01' )
		and exists(select 1 
					from ca110 b ,qd110 c
					where b.objid=c.ca110_id 
					and a.objid = b.ca100_id 
					and b.status in(6,7)
					and c.qd100_id in(select objid from qd100 where code_type in('B','C','D','G','E','F'))
		)
		and x.cntcode=a.inform_cntcode
	)
	FROM @G_F_FHRP01 x