Oracle两表关联更新三种方式
<p>创建如下表数据</p><pre>select * from t1 ;
</pre>
<p></p>
<pre>select * from t2;
</pre>
<p></p>
<p>现需求：参照T2表，修改T1表，修改条件为两表的fname列内容一致。</p>
<h2><strong>方式1. update</strong></h2>
<pre>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);
</pre>
<p></p>
<h2><strong>方式2. 内联视图更新</strong></h2>
<pre>UPDATE (
select t1.fmoneyfmoney1,t2.fmoneyfmoney2 from t1,t2 where t1.fname = t2.fname
)t
set fmoney1 =fmoney2;
</pre>
<h2><strong>方式3. merge更新</strong></h2>
<pre>merge into t1
using (select t2.fname,t2.fmoney from t2) t
on (t.fname = t1.fname)
when matched then
updateset t1.fmoney = t.fmoney;
</pre>
<p class="post-copyright">未经允许不得转载：<ahref="https://www.wangchao.info/">王超博客</a> » <ahref="https://www.wangchao.info/2354.html">Oracle两表关联更新三种方式</a></p>
页:
[1]