两表关联更新、删除

两表关联更新

通用方法

update test1 
set name=(select name from test2 where test2.id=test1.id),
age=(select age from test2 where test2.id=test1.id)

MySql

update test1,test2 
set test1.name=test2.name,test1.age=test2.age
where test1.id=test2.id

Oracle

方法一：update

UPDATE T1 
SET T1.FMONEY = (select T2.FMONEY from t2 where T2.FNAME = T1.FNAME)
WHERE EXISTS(SELECT 1 FROM T2 WHERE T2.FNAME = T1.FNAME)

方法二：merge

merge into t1
using (select t2.fname,t2.fmoney from t2) t
on (t.fname = t1.fname)
when matched then 
  update  set t1.fmoney = t.fmoney;

方法三：内联视图更新

UPDATE (
select t1.fmoney  fmoney1,t2.fmoney  fmoney2 from t1,t2 where t1.fname = t2.fname
)t
set fmoney1 =fmoney2;

两表关联删除