1,insert 和update是可选的 2,UPDATE 和INSERT 后面可以跟WHERE 子句 3,在ON条件中可以使用常量来insert 所有的行到目标表中,不需要连接到源表和目标表 4,UPDATE 子句后面可以跟delete 来去除一些不需要的行。
举例:
-
create table PRODUCTS
- (
-
PRODUCT_ID INTEGER,
- PRODUCT_NAME VARCHAR2(60),
- CATEGORY VARCHAR2(60)
- );
-
insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
-
insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
-
insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
-
insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
-
insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
-
commit;
-
create table NEWPRODUCTS
- (
-
PRODUCT_ID INTEGER,
- PRODUCT_NAME VARCHAR2(60),
- CATEGORY VARCHAR2(60)
- );
-
insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
-
insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
-
insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
-
insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
-
commit;
-
1,可省略的update 或者insert
- MERGE INTO products p
- 2 USING newproducts np
-
3 ON (p.product_id = np.product_id)
-
4 WHEN MATCHED THEN
-
5 UPDATE
-
6 SET p.product_name = np.product_name,
-
7 p.category = np.category;
使用表newproducts中的product_name 和category字段来更新表products 中相同product_id的product_name 和category.
2,当条件不满足的时候把newproducts表中的数据INSERT 到表products中。
- MERGE INTO products p
- USING newproducts np
-
ON (p.product_id = np.product_id)
-
WHEN NOT MATCHED THEN
-
INSERT
-
VALUES (np.product_id, np.product_name,
- np.category);
3,带条件的insert 和update
- MERGE INTO products p
- USING newproducts np
-
ON (p.product_id = np.product_id)
-
WHEN MATCHED THEN
-
UPDATE
-
SET p.product_name = np.product_name
-
WHERE p.category = np.category;
insert 和update 都带有where 字句
- MERGE INTO products p
- USING newproducts np
-
ON (p.product_id = np.product_id)
-
WHEN MATCHED THEN
-
UPDATE
-
SET p.product_name = np.product_name,
- p.category = np.category
-
WHERE p.category = 'DVD'
-
WHEN NOT MATCHED THEN
-
INSERT
-
VALUES (np.product_id, np.product_name, np.category)
-
WHERE np.category != 'BOOKS'
4,无条件的insert
- MERGE INTO products p
- USING newproducts np
-
ON (1=0)
-
WHEN NOT MATCHED THEN
-
INSERT
-
VALUES (np.product_id, np.product_name, np.category)
-
WHERE np.category = 'BOOKS'
5,delete 子句
1 merge into products p
2 using newproducts np
3 on(p.product_id = np.product_id)
4 when matched then
5 update
6 set p.product_name = np.product_name
7 delete where category = 'macle1_cate';
select *
from products;
PRODUCT_ID PRODUCT_NAME CATEGORY
--------------------------------------- -------------------- --------------------
1502 macle22 macle2_cate
1503 macle3 macle2_cate
1504 macle macle1_cate
1505 macle5 macle5_cate
1504 中的macle1_cate 满足delete where,但是不满足 on 中的条件,所以没有被删除。!!!!!!重点
-----------------------------------------------
动机:
想在Oracle中用一条SQL语句直接进行Insert/Update的操作。
说明:
在进行SQL语句编写时,我们经常会遇到大量的同时进行Insert/Update的语句 ,也就是说当存在记录时,就更新(Update),不存在数据时,就插入(Insert)。
实战:
接下来我们有一个任务,有一个表T,有两个字段a,b,我们想在表T中做Insert/Update,如果存在,则更新T中b的值,如果不存在,则插入一条记录。在Microsoft的SQL语法中,很简单的一句判断就可以了,SQL Server中的语法如下:
if exists(select 1 from T where T.a='1001' ) update T set T.b=2 Where T.a='1001' else insert into T(a,b) values('1001',2);
以上语句表明当T表中如果存在a='1001' 的记录的话,就把b的值设为2,否则就Insert一条a='100',b=2的记录到T中。
但是接下来在Oracle中就遇到麻烦了,记得在Oracle 9i之后就有一条Merge into 的语句可以同时进行Insert 和Update的吗,Merge的语法如下:
MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE table_name
SET col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);
上面的语法大家应该都容易懂吧,那我们按照以上的逻辑再写一次。
MERGE INTO T T1
USING (SELECT a,b FROM T WHERE t.a='1001') T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
UPDATE SET T1.b = 2
WHEN NOT MATCHED THEN
INSERT (a,b) VALUES('1001',2);
以上的语句貌似很对是吧,实际上,该语句只能进行更新,而无法进行Insert,错误在哪里呢?
其实在Oracle中Merge语句原先是用来进行整表的更新用的,也就是ETL工具比较常用的语法,重点是在Using上。
用中文来解释Merge语法,就是:
在alias2中Select出来的数据,每一条都跟alias1进行 ON (join condition)的比较,如果匹配,就进行更新的操作(Update),如果不匹配,就进行插入操作(Insert)。
因此,严格意义上讲,”在一个同时存在Insert和Update语法的Merge语句中,总共Insert/Update的记录数,就是Using语句中alias2的记录数。”
以上这句话也就很好的解释了在上面写的语句为何只能进行Update,而不能进行Insert了,因为都Select不到数据,如何能进行Insert呢:)
接下来要改成正确的语句就容易多了,如下:
MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN
INSERT (a,b) VALUES(T2.a,T2.b);
查询结果,OK!
注意:
如果不懂Merge语句的原理,Merge语句是一条比较危险的语句,特别是在您只想更新一条记录的时候,因为不经意间,你可能就把整表的数据都Update了一遍.....汗!!!
我曾经犯过的一个错误如下所示,大家看出来是什么问题了吗?
MERGE INTO T T1
USING (SELECT Count(*) cnt FROM T WHERE T.a='1001') T2
ON (T2.cnt>0)
WHEN MATCHED THEN
UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN
INSERT (a,b) VALUES(T2.a,T2.b);
相关推荐
在Oracle数据库中,`MERGE INTO`语句是一种强大的数据操作工具,它允许你在一个步骤中执行更新(UPDATE)和插入(INSERT)操作,从而提高了数据处理的效率和灵活性。这个命令尤其适用于需要同步两个表的数据时,比如...
标题:Oracle Merge Into 的使用 描述详解:Oracle Merge Into 是一个功能强大的SQL语句,它在Oracle数据库系统中被广泛用于处理数据同步、更新或插入操作。与传统的UPDATE和INSERT语句相比,Merge Into 提供了更为...
在Oracle数据库中,`MERGE INTO`语句是一种非常强大的工具,它允许用户根据源表中的数据更新目标表,同时也可以插入新记录。这一特性在处理大量数据时尤其有用,因为它能够在一个步骤中完成更新和插入操作,从而提高...
Oracle 数据库中 Merge Into 的用法 在 Oracle 数据库中,Merge Into 语句是一种非常有用的功能,它主要用来合并 update 和 insert 语句,即用一个表中的数据来修 改或插入到另一个表中。Merge Into 语句的主要原则...
首先,需要使用Oracle 9i及其以后版本支持的merge into语句,该语句可以实现insertOrUpdate的功能。然后,使用Mybatis的动态SQL语法foreach循环插入,待插入的实体bean的List通过查询数据库dual形成表。foreach的 ...
Oracle中的`MERGE INTO`语句是一种强大的工具,用于整合数据操作,如更新、插入和删除,...需要注意的是,`MERGE INTO`可能会触发并发控制问题,因此在多用户环境中使用时需要谨慎,确保事务处理和并发控制策略适当。
在Oracle数据库系统中,死锁是一种常见的并发问题,它会导致多个事务之间互相等待对方释放资源而无法继续执行,最终导致整个系统的运行效率降低甚至停滞。死锁不仅影响数据库性能,还会影响应用程序的可用性和用户...
3. **支持常量过滤谓词**:可以在`ON`条件中使用常量过滤谓词,这意味着即使不连接源表和目标表也可以插入所有行到目标表中。 4. **支持DELETE子句**:`MERGE`语句还可以跟`DELETE`子句结合使用,以便删除某些不符合...
本文将详细介绍 Oracle 中 `MERGE` 关键字的使用方法,并结合实际示例来解析其工作原理。 #### 二、`MERGE` 语句的基本概念 在 Oracle 9i R2 版本之前,如果需要同时进行更新和插入操作,开发人员通常需要编写两条...
详细介绍了使用 BULK COLLECT 进行批量操作 提高sql的执行效率 使用MERGE INTO USING 一条sql搞定 新增和修改 使用connect by 进行递归树查询
Oracle Merge 函数是一种强大的数据操作语句,用于从一个表中选择一些数据更新或者插入到另一个表中。Merge 函数的使用方式有三种:只更新不插入、只插入不更新和既插入也更新。 Merge 函数的基本用法 Merge ...
下面将详细阐述如何使用这两个工具以及如何通过`MERGE INTO`语句进行数据合并更新。 1. SQLULDR2导出数据: SQLULDR2是Oracle提供的一款数据导出工具,它能够快速地将数据库中的数据导出到文本文件或其他格式的...
如果不使用Oracle提供的merge语法的话,可能先要上数据库select查询一下看是否存在,然后决定怎么操作,这样的话需要写更多的代码,同时性能也不好,要来回数据库两次.使用merge的话则可以一条SQL语句完成. 1)主要功能 ...
Oracle的`MERGE INTO`语句是一种强大的工具,用于根据源数据更新或插入目标表的记录。这个SQL命令结合了`INSERT`, `UPDATE`和`DELETE`操作,使得在处理数据匹配和同步时更加高效。在给定的实例中,`MERGE INTO`被...
merge 详解 及实例 优点: — 避免了分开更新 — 提高性能并易于使用 — 在数据仓库应用中十分有用 — 使用merge比传统的先判断再选择插入或更新快很多
如果需要在MySQL中实现记录不存在则insert,不存在则update操作。可以使用以下语句: 更新一个字段: INSERT INTO tbl (columnA,columnB,columnC) VALUES (1,2,3) ON DUPLICATE KEY UPDATE columnA=IF(columnB>0,...