Merge Into
MERGE是Oracle 9i引入了的命令,类似于Mysql里的insert into on duplicate key。在Oracle 10g中MERGE有些改进。
Merge可以完成以下功能:
1、两个表之间数据的更新
2、进行进销存更新库存
3、进行表之间数据的复制
MERGE INTO [your table-name] [rename your table here]
USING ( [write your query here] )[rename your query-sql and using just like a table]
ON ([conditional expression here] AND [...]...)
WHEN MATHED THEN
[here you can execute some update sql or something else ]
WHEN NOT MATHED THEN
[execute something else here ! ]
1、在语句结束后一定要用分号,否则会提示错误。
2、Merge后为目标表,Using后为数据源表
3、如果有两个When matched,则必须使用and来限定第一个子句,一个子句必须制定一个update,另一个必须制定delete
4、When not matched by target,这个子句处理存在于数据源之中,但不存在目标之中的数据行。
5、When not matched等价于When not matched by target
6、When not mathed by source,这个子句处理,存在于目标中,但是不存在数据表之中的数据行
实验:
1.在空表中执行:
CREATE TABLE T2(ID INT,Score INT,Val float,Primary Key(ID))
IF EXISTS(SELECT 1 FROM T2 WHERE ID=1)
UPDATE T2 SET Val=1 WHERE ID=1
ELSE
INSERT INTO T2(ID,Score,Val)
SELECT 1,95,0.8
MERGE INTO T2 AS T2
USING (SELECT 2 AS ID)AS T1
ON (T2.ID=T1.ID)
WHEN MATCHED THEN
UPDATE SET Val=1--不是UPDATE T2 SET
WHEN NOT MATCHED THEN
INSERT(ID,Score,Val)--不是INSERT INTO T2
VALUES (2,99,0.9);--要加分号
2.在10000条记录表中执行:
IF EXISTS(SELECT 1 FROM T1 WHERE ID=633610468318)
UPDATE T1 SET Hits=100 WHERE ID=633610468318
INSERT INTO T1(ID,Hits)
SELECT 633610468318,100
MERGE INTO T1 AS T1
USING (SELECT 633610468318 AS ID)AS T2
UPDATE SET Hits=100
INSERT(ID,Hits)
VALUES (633610468318,100);
3.在100万数据下:
CREATE TABLE T3(ID INT,Score INT,Val float,Primary Key(ID))
DECLARE @CNT INT
SET @CNT=1
WHILE @CNT<=1000000
BEGIN
INSERT INTO T3(ID,Score,Val)
SELECT @CNT,RAND()*100,RAND()
SET @CNT=@CNT+1
END
IF EXISTS(SELECT 1 FROM T3 WHERE ID=999993)
UPDATE T3 SET Val=0.8 WHERE ID=999993
SELECT 999993,66,0.8
MERGE INTO T3 AS T1
USING (SELECT 999993 AS ID)AS T2
UPDATE SET Val=0.8
INSERT(ID,Score,Val)
VALUES (999993,66,0.8);
实验结果发现,MERGE INTO效率并没有IF的高!!!
实验:同时处理12个
USING (SELECT ID FROM T3 WHERE ID>999989)AS T2
VALUES (T2.ID,66,0.8);
结论:MERGE对于批量处理有天然的优势,可以节省代码量,也可以有更好的效率
应用:在一些有子查询批量UPDATE中,可以考虑用MERGE优化
① http://www.360doc.com/content/12/0816/22/10443322_230582399.shtml
② http://blog.csdn.net/gongjian0628/article/details/12751761