- 浏览: 287861 次
- 性别:
- 来自: 无锡
文章分类
- 全部博客 (100)
- swing (6)
- web (13)
- Eclipse (5)
- plug-in (0)
- mysql (3)
- java综合 (13)
- 反编译 (2)
- oracle (16)
- uml (1)
- 编码相关 (2)
- tomcat (2)
- gis (2)
- windows (8)
- ssh (1)
- android (9)
- LBS (1)
- 笔记 (1)
- struts2 (1)
- http (1)
- 安全 (5)
- vps (1)
- linux (3)
- dwr (1)
- jni (1)
- js (2)
- 支付宝 (1)
- 基础与原理 (4)
- maven (3)
- sso (1)
- 数字证书 (2)
- keytool (1)
最新评论
-
wgyyouge:
有个命令行下的高效迁移工具ora2mysqlhttp://ww ...
强大简单的mysql迁移到oracle的工具 -
qqwe8554677:
...
java汉字转拼音,取汉字首字母,支持繁体 -
相约的旋律:
最后一个结论有疑问。我们在生产服务器上面一开始是使用 in 查 ...
SQL in 和 exists区别(转)(数据量大,效率区别特别明显) -
Seavision:
怎么输出大写?
java汉字转拼音,取汉字首字母,支持繁体 -
诗飘秋舞的活着:
输入 长沙的时候 输出的是 zhangsha 和zs
java汉字转拼音,取汉字首字母,支持繁体
转自:http://dikar.iteye.com/blog/797321
我们经常遇到的一个需求是,先判断数据是否存在,如果存在则更新,否则就插入,以前比较土是用java自己做了。这个在多线程或者多机的情况下就会有些问题,有时候还得让db报个唯一性约束才行。
最近和一个同事(以前是oracle的)做项目,发现他写了个牛逼的sql(或者说自己见识太短浅了),特此膜拜下
类似这样的
MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN MATCHED THEN UPDATE SET a.status = b.status WHERE b.status != 'VALID' WHEN NOT MATCHED THEN INSERT (object_id, status) VALUES (b.object_id, b.status) WHERE b.status != 'VALID';
查了些资料发现这种merge sql早就被支持了,只是自己还不知道而已。
<--------------------------------------不华丽的分界线--------------------------------------------------------------------->
例如wiki上的 http://en.wikipedia.org/wiki/Merge_%28SQL%29
Merge (SQL)
It has been suggested that Upsert be merged into this article or section. (Discuss ) |
A relational database management system uses SQL MERGE
(upsert ) statements to INSERT
new records or UPDATE
existing records depending on whether or not a condition matches. It was officially introduced in the SQL:2008 standard.
Contents[hide ] |
Usage
MERGE INTO table_name USING table_reference ON (condition ) WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...
Other non-standard implementations
Some other database management systems support this, or very similar behavior, through their own, non-standard SQL extensions.
MySQL , for example, supports the use of INSERT ... ON DUPLICATE KEY UPDATE
syntax[ 1] which can be used to achieve the same effect. It also supports REPLACE INTO
syntax[ 2] , which first deletes the row, if exists, and then inserts the new one.
SQLite 's INSERT OR REPLACE INTO
works similarly.
Firebird supports both MERGE INTO
and a single-row version, UPDATE OR INSERT INTO tablename (columns) VALUES (values) [MATCHING (columns)]
, but the latter does not give you the option to take different actions on insert vs. update (e.g. setting a new sequence value only for new rows, not for existing ones.)
References
- ^ MySQL :: MySQL 5.1 Reference Manual :: 12.2.4.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax
- ^ MySQL 5.1 Reference Manual: 11.2.6 REPLACE Syntax
External links
- HyperSQL (HSQLDB) documentation
- Oracle 11g Release 2 documentation on MERGE
- Firebird 2.1 documentation on MERGE
- DB2 v9 MERGE statement
- SQL Server 2008 documentation
- H2 (1.2) SQL Syntax page
<---------------------------------------------不华丽的分界线--------------------------------------------------------------->
同时也找了
http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php
MERGE Statement Enhancements in Oracle Database 10g
Oracle 10g includes a number of amendments to the MERGE
statement making it more flexible.
Test Table
The following examples use the table defined below.
CREATE TABLE test1 AS SELECT * FROM all_objects WHERE 1=2;
Optional Clauses
The MATCHED
and NOT MATCHED
clauses are now optional making all of the following examples valid.
-- Both clauses present. MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN MATCHED THEN UPDATE SET a.status = b.status WHEN NOT MATCHED THEN INSERT (object_id, status) VALUES (b.object_id, b.status); -- No matched clause, insert only. MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN NOT MATCHED THEN INSERT (object_id, status) VALUES (b.object_id, b.status); -- No not-matched clause, update only. MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN MATCHED THEN UPDATE SET a.status = b.status;
Conditional Operations
Conditional inserts and updates are now possible by using a WHERE
clause on these statements.
-- Both clauses present. MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN MATCHED THEN UPDATE SET a.status = b.status WHERE b.status != 'VALID' WHEN NOT MATCHED THEN INSERT (object_id, status) VALUES (b.object_id, b.status) WHERE b.status != 'VALID'; -- No matched clause, insert only. MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN NOT MATCHED THEN INSERT (object_id, status) VALUES (b.object_id, b.status) WHERE b.status != 'VALID'; -- No not-matched clause, update only. MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN MATCHED THEN UPDATE SET a.status = b.status WHERE b.status != 'VALID';
DELETE Clause
An optional DELETE WHERE
clause can be used to clean up after a merge operation. Only those rows which match both the ON
clause and the DELETE WHERE
clause are deleted.
MERGE INTO test1 a USING all_objects b ON (a.object_id = b.object_id) WHEN MATCHED THEN UPDATE SET a.status = b.status WHERE b.status != 'VALID' DELETE WHERE (b.status = 'VALID');
For further information see:
发表评论
-
大数据量提高性能变通方法
2012-05-11 10:43 1082大数据量需要变通 方法之一如下: 可以使用 row_n ... -
SQL in 和 exists区别(转)(数据量大,效率区别特别明显)
2012-02-03 15:35 8184来源: 薛波的日志 in 和 exist ... -
SQL效率之索引
2012-02-03 15:34 1545一、关于索引的知识要写出运行效率高的sql,需要对索引的机制有 ... -
sqlplus登录
2011-09-30 16:42 13661.直接敲sqlplus并回车就是启动SQL*PLUS,输入u ... -
字符转换CLOB插入数据库
2011-09-19 09:26 1172public static CLOB oracleStr2Cl ... -
oracle instr ,case when
2011-04-13 16:31 1347今天新学了一个函数instr(str1,str2,m,n); ... -
Oracle的转义字符需要用ESCAPE函数来定义
2011-03-28 11:21 4378SQL> create table t_char(a ... -
java调用存储过程返回的结果集(游标)
2011-03-25 11:46 2272新建存储过程 create or replace pr ... -
头一回写这么麻烦的oracle查询语句,请高手轻拍
2011-03-19 19:32 1194今天同事遇到一问题,写查询不知如何下手,因为查询某条详细信息时 ... -
临时表空间不足ora-01652
2011-03-01 15:08 1452非临时表空间: select file_id fro ... -
oracle实用语句及函数记录
2011-02-18 20:43 989查询插入:insert into table_A(col1,c ... -
Oracle因主机名或IP变动,导致EM无法启动的问题。
2011-02-14 16:17 2061错误信息: WIN的事件查看器: An erro ... -
Oracle建表步骤
2011-02-12 15:06 1095create tablespace x datafile 'x ... -
ORACLE分页查询
2011-01-24 17:08 1103select * from (select id,rown ... -
强大简单的mysql迁移到oracle的工具
2011-01-13 10:25 20928http://www.5stardatabasesoftwar ...
相关推荐
MERGE语句是SQL语句的一种。在SQL Server、Oracle数据库中可用,MySQL、PostgreSQL中不可用。MERGE是Oracle9i新增的语法,用来合并UPDATE和INSERT语句。通过MERGE语句,根据一张表(原数据表,source table)或子...
MERGE语句在SQL中扮演着关键角色,它提供了一种高效且灵活的方式来合并两个数据集,无论是更新、插入还是删除,都能一气呵成。本文将深入探讨MERGE语句的使用,以及在不支持MERGE的数据库环境中可能采取的替代策略。...
详细介绍了使用 BULK COLLECT 进行批量操作 提高sql的执行效率 使用MERGE INTO USING 一条sql搞定 新增和修改 使用connect by 进行递归树查询
`Merge`函数是SQL Server 2008引入的一个强大的数据操纵语言(DML)关键字,它可以合并`Insert`、`Update`和`Delete`操作到一个单一的语句中,极大地提高了数据库维护的效率和代码的简洁性。在本文中,我们将深入...
Merge语句是一种复杂的SQL操作,它结合了INSERT、UPDATE和DELETE,用于同步两个数据源。当Merge触发器被定义为在这些操作上触发时,如果处理不当,可能会导致一次性处理大量行,而非逐行处理。这可能导致资源消耗过...
merge_sql_files
sql学习 merge误区探讨.sql
sql学习 merge经典案例.sql
Mybatis批量foreach merge into的用法是通过Mybatis的动态SQL语法foreach循环插入实现的,这种方法可以批量插入时间价格表数据。如果某个套餐的某天的价格存在,则更新,不存在则插入。下面是该方法的详细介绍: ...
如数据变化跟踪、同步编程模型、冲突检测、FILESTREAM数据类型、集成全文本搜索、稀疏列、巨大...MERGE SQL语句、数据概况、星形连接优化、企业报表引擎、互联网报表部署、锁定计算、BI平台管理和导出到Word和Excel等...
sql学习 merge灵活之处.sql
sql学习 merge有啥用途.sql
上述配置中,`druid.stat.filter.enabled`开启StatFilter,`druid.stat.mergeSql`表示是否合并相同的SQL,`druid.stat.slowSqlMillis`定义了慢SQL的阈值,超过这个时间的SQL会被记录下来。 此外,StatFilter还提供...
0709-动态SQL-merge-into.sql
<property name="mergeSql" value="true"/> ``` Druid的另一个亮点是其SQL解析能力。通过Druid的Parser组件,可以将SQL语句解析成抽象语法树(AST),便于进行SQL格式化、校验和优化。这对于保持代码整洁、避免...
"Oracle Merge 函数详解" Oracle Merge 函数是一种强大的数据操作语句,用于从一个表中选择一些数据更新或者插入到另一个表中。Merge 函数的使用方式有三种:只更新不插入、只插入不更新和既插入也更新。 Merge ...
- `filters`:全局过滤器,可配置如`mergeSql`(合并SQL)等功能。 - `validationQuery`:用于验证连接是否有效的SQL语句。 - `poolPreparedStatements`:是否缓存PreparedStatement对象。 - `...