关于ORACLE中插入,更新(MERGE)的使用
Oracle9i引入了一个新的SQL语句,使用本语句可以在一条语句中连接两个表。
以前要使用UPDATE去更新两个表中都存在的记录,或者用INSERT添加两个合并表中不存在的记录,必须写两个SQL语句,
而现在这两种操作都只要一条SQL MERGE语句就可以实现。
![](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)
![](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)
![](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)
![](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/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
执行的结果是shipment数据已经被合并到inventory中,所以与inventory中某些东西相匹配的shipment都会被添加到count中,
而没有得到的匹配的就不会添加到inventory中。
在MERGE语句中必须指定一个WHEN MATCHED和一个WHEN NOT MATHCED语句。
如果除这两种情况之外还有别的情况,你可能就需要使用一个常规的INSERT或者UPDATE语句。
另外一点是MERGE语句一次只能修改一行记录,而且不能修改在ON子句中引用的列。
MERGE语句的目标表(target table)(在本例中是inventory)必须是一个可以使用INSERT语句进行插入或者UPDATE语句进行更新的表或者视图。
源表(source table)(在本例中是shipment)可以是任何的查询表,比如说外部表或者管道化表函数。
posted on 2006-11-05 17:22 changhai-xuri 阅读(599) 评论(0) 编辑 收藏 举报