【转】 ORACLE中的多表关联更新
假设我们有test1 ,test2 两表
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
至此:
test1
NO NAME
---------- ----------
1 a
2 b
3 c
test2
NO NAME
---------- ----------
1 aa
2 bb
如果要将test1表与test2表NO字段相等的记录的name字段更新为与test2表中的name字
段的值.
即以下效果:
test1
NO NAME
---------- ----------
1 aa
2 bb
3 c
那么以下的语句
update test1 a set name=(select name from test2 b where a.no=b.no);
的效果为:
test1
NO NAME
---------- ----------
1 aa
2 bb
3
要出想要的效果,可以用下面的语句来实现.
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
也可以用下面的语句来实现:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
不过有个前提,是给test2表的NO设为主键.
alter table test2 add primary key(no);
原文地址:http://www.cnblogs.com/rayman/archive/2005/03/27/126527.html?login=1#commentform