MERGE的使用一些注意事项
来源:互联网 发布:西海岸新区知乎 编辑:程序博客网 时间:2024/04/29 19:12
官方文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9016.htm#SQLRF01606
MERGE
Purpose
Use the MERGE
statement to select rows from one ormore sources for update or insertion into a table or view. You canspecify conditions to determine whether to update or insert intothe target table or view.
This statement is a convenient way to combine multipleoperations. It lets you avoid multipleINSERT
,UPDATE
, and DELETE
DML statements.
MERGE
is a deterministic statement. That is, youcannot update the same row of the target table multiple times inthe sameMERGE
statement.
Note:
Oracle Database does not implement fine-grained access controlduringMERGE
statements. If you are using thefine-grained access control feature on the target table or tables,use equivalentINSERT
and UPDATE
statements instead of MERGE
to avoid error messagesand to ensure correct access control.Prerequisites
You must have the INSERT
and UPDATE
object privileges on the target table and theSELECT
object privilege on the source table. To specify theDELETE
clause of themerge_update_clause
, you mustalso have theDELETE
object privilege on the targettable.
merge_update_clause
The merge_update_clause
specifiesthe new column values of the target table. Oracle performs thisupdate if the condition of theON
clause is true. Ifthe update clause is executed, then all update triggers defined onthe target table are activated.
Specify the where_clause
if you want thedatabase to execute the update operation only if the specifiedcondition is true. The condition can refer to either the datasource or the target table. If the condition is not true, then thedatabase skips the update operation when merging the row into thetable.
Specify the DELETE
where_clause
to clean up data ina table while populating or updating it. The only rows affected bythis clause are those rows in the destination table that areupdated by the merge operation. That is, the DELETE
WHERE
condition evaluates the updated value, not theoriginal value that was evaluated by theUPDATE
SET
... WHERE
condition. If a row of thedestination table meets theDELETE
condition but isnot included in the join defined by the ON
clause,then it is not deleted. Any delete triggers defined on the targettable will be activated for each row deletion.
You can specify this clause by itself or with themerge_insert_clause
. If youspecify both, then they can be in either order.
Restrictions on themerge_update_clause This clause is subject tothe following restrictions: --在update字句中的限制
You cannot update a column that is referenced in the
ON
condition
clause.--你不能更新在ON
condition字句中被引用的一个列。
You cannot specify
DEFAULT
when updating aview.当更新一个试图时候你不能够指定默认
- MERGE的使用一些注意事项
- DB2的MERGE语句使用注意事项!
- DB2的MERGE语句使用注意事项!
- 使用excel的一些注意事项
- 缓存使用的一些注意事项
- 使用MediaPlayer的一些注意事项
- 使用idm的一些注意事项
- LigerUI使用的一些注意事项
- 缓存使用的一些注意事项
- struts使用的一些注意事项
- 使用Opencv的一些注意事项
- pig使用的一些注意事项
- Oracle使用Merge into 注意事项
- Mysql Merge表的使用注意事项和优点介绍
- Mysql Merge表的使用注意事项和优点介绍
- Mysql Merge表的使用注意事项和优点介绍
- Pandas:merge函数使用注意事项(pandas的merge函数造成大量错误的空值)
- Eclipse中使用CVS 的一些注意事项
- interval day to second和interval year to month数据类型
- Oracle的时区问题
- hibernate中的缓存机制
- oracle 迁移数据方案(定制数据库模板)
- 详解GROUPING函数
- MERGE的使用一些注意事项
- 关于运算符IN、ANY、ALL、NOT、SOME
- WITH引出的子查询扇出子句
- 关于序列
- 关于外部表----external_table_clause
- Insert语句的语法
- NATURAL JOIN--自然连接的一些注意事项
- create table .... as注意事项
- update语句的语法