逐行update

当遇到这样的需求时：

update a set a1=(select b1 from b where b2=a2)

我折腾了很久，终于想到了一个通用的方法

UPDATE t1 SET 
Column1=(SELECT ColumnA FROM t2 WHERE t2.key=t1.key), 
Column2=(SELECT ColumnB FROM t2 WHERE t2.key=t1.key), 
WHERE t1.key=(SELECT key FROM t2 WHERE t2.key=t1.key)

举例：

update qm set 

gqmj=gqmj-(select bgmjzzgq from DLBGJCTJB where substr(BGHDLBM,1,3)<>qm.dl_bm 

and substr(BGQDLBM,1,3)||BGQQSXZ||BGQZLDWDM||BGQGDPDJ||BGQGDLX=

qm.dl_bm||qm.qs_xz||qm.zl_dm||qm.gd_pdj||qm.gd_lx)

where qm.dl_bm||qm.qs_xz||qm.zl_dm||qm.gd_pdj||qm.gd_lx =

(select substr(BGQDLBM,1,3)||BGQQSXZ||BGQZLDWDM||BGQGDPDJ||BGQGDLX 

from DLBGJCTJB where 

substr(BGHDLBM,1,3)<>qm.dl_bm 

and substr(BGQDLBM,1,3)||BGQQSXZ||BGQZLDWDM||BGQGDPDJ||BGQGDLX=

qm.dl_bm||qm.qs_xz||qm.zl_dm||qm.gd_pdj||qm.gd_lx)

好像和oracle的merge有点像，这个以后再研究