Oracle中MERGE INTO用法及实例解析

MERGE INTO介绍

MERGE INTO用于将一台表的数据插入到另一个表中，可以根据条件进行更新或插入操作。

MERGE INTO需要指定源表和目标表，并且在ON子句中指定源表和目标表的关联条件，然后我们可以在WHEN MATCHED或WHEN NOT MATCHED子句中指定更新或插入操作。

MERGE INTO语法

MERGE INTO [目标表名] T1
USING [源表名] T2
ON ([条件])
WHEN MATCHED THEN
    UPDATE SET T1.col1 = T2.col1, T1.col2 = T2.col2, ...
WHEN NOT MATCHED THEN
    INSERT (T1.col1, T1.col2, ...)VALUES (T2.col1, T2.col2, ...);

MERGE INTO实例解析

示例一

现有两个表，一个表示学生信息，一个表示学生成绩信息，需要将学生成绩信息更新到学生信息表中。

学生信息表student：

学生ID	姓名	年龄	班级
1	张三	18	1
2	李四	17	1
3	王五	18	2

学生成绩表score：

学生ID	语文成绩	数学成绩	英语成绩
1	90	85	95
2	95	80	90
3	85	90	95
4	80	75	85

我们需要将学生成绩更新到学生信息表中，如果学生信息表中有对应学生ID的记录，则更新该记录的成绩信息；如果学生信息表中没有对应学生ID的记录，则插入一条新记录。

MERGE INTO student T1
USING score T2
ON (T1.学生ID = T2.学生ID)
WHEN MATCHED THEN
    UPDATE SET T1.语文成绩 = T2.语文成绩,
               T1.数学成绩 = T2.数学成绩,
               T1.英语成绩 = T2.英语成绩
WHEN NOT MATCHED THEN
    INSERT (T1.学生ID, T1.语文成绩, T1.数学成绩, T1.英语成绩)
    VALUES (T2.学生ID, T2.语文成绩, T2.数学成绩, T2.英语成绩);

执行上面的SQL语句后，可以得到新的学生信息表student：

学生ID	姓名	年龄	班级	语文成绩	数学成绩	英语成绩
1	张三	18	1	90	85	95
2	李四	17	1	95	80	90
3	王五	18	2	85	90	95
4	NULL	NULL	NULL	80	75	85

在学生信息表中新增了一条学生ID为4的记录。

示例二

现有两个表，一个记录售卖记录，一个记录退货记录，需要根据销售和退货记录来更新库存表中的数据。

售卖记录表sales：

产品ID	销售数量	价格
1	100	10
2	200	20
3	300	30

退货记录表returns：

产品ID	退货数量
1	20
2	40

库存表inventory：

产品ID	数量	价格
1	80	10
2	160	20
3	300	30

需要根据销售和退货记录来更新库存表中的数据，如果产品ID在库存表中不存在，则插入一条新纪录。

MERGE INTO inventory T
USING (
  SELECT 产品ID, SUM(销售数量-退货数量) AS 数量, 价格 
  FROM (
    SELECT 产品ID, 销售数量, 价格, 0 AS 退货数量 FROM sales
    UNION ALL
    SELECT 产品ID, 0 AS 销售数量, 0-退货数量 AS 价格 FROM returns
  ) T
  GROUP BY 产品ID, 价格
) S
ON (T.产品ID = S.产品ID AND T.价格 = S.价格)
WHEN MATCHED THEN 
  UPDATE SET T.数量 = T.数量 + S.数量
WHEN NOT MATCHED THEN 
  INSERT (T.产品ID, T.数量, T.价格) VALUES (S.产品ID, S.数量, S.价格);

执行上面的SQL语句后，可以得到新的库存表inventory：

产品ID	数量	价格
1	60	10
2	120	20
3	300	30

更新了产品ID为1和2的数量信息，并新增了一条产品ID为3的记录。