MERGE

最新推荐文章于 2024-05-19 17:03:37 发布

cocug66420

最新推荐文章于 2024-05-19 17:03:37 发布

阅读量72

点赞数

文章标签：数据库

1、Purpose

Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view.This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.MERGE is a deterministic statement. You cannot update the same row of the target table multiple times in the same MERGE statement.

大概就是说，用merge可以选择出一个或多个源码去用来更新或插入到表中或视图中。你可以指定条件来决定是否更新或插入到目标表或视图中。Merge用来进行多项DML组合操作是很方便的。

2、Prerequisites

You must have the INSERT and UPDATE object privileges on the target table and the SELECT object privilege on the source table. To specify the DELETEclause of the merge_update_clause, you must also have the DELETE object privilege on the target table.

前提就是，你必须在目的表上有insert,update,delete的权限，在源表上有select的权限

3、Syntax

MERGE [ hint ]

INTO [ schema. ] { table | view } [ t_alias ]

USING { [ schema. ] { table | view }

| subquery

} [ t_alias ]

ON ( condition )

[ merge_update_clause ]

[ merge_insert_clause ]

[ error_logging_clause ] ;

WHEN MATCHED THEN

UPDATE SET column = { expr | DEFAULT }

[, column = { expr | DEFAULT } ]...

[ where_clause ]

[ DELETE where_clause ]

WHEN NOT MATCHED THEN

INSERT [ (column [, column ]...) ]

VALUES ({ expr | DEFAULT }

[, { expr | DEFAULT } ]...

)

[ where_clause ]

LOG ERRORS

[ INTO [schema.] table ]

[ (simple_expression) ]

[ REJECT LIMIT { integer | UNLIMITED } ]

4、Examples

Merging into a Table:

CREATE TABLE bonuses (employee_id NUMBER, salary NUMBER DEFAULT 100);

INSERT INTO bonuses(employee_id)

(SELECT e.employee_id FROM employees e, orders o

WHERE e.employee_id = o.sales_rep_id

GROUP BY e.employee_id);

创建一个bonuses表默认值为100

SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID salary

----------- ----------

153 100

154 100

155 100

156 100

158 100

159 100

160 100

161 100

163 100

我想做的就是,把employees table中部门为80中salary>8000的数据删除，<=的则以原来salary的0.1插入到bonuses中，同时对bonuses中的salary更新为在原有salary上加上employees中对应salary的0.01.

Merge into bonuses d

Using(select employee_id,salary,department_id from employees where department_id=80) s

On(d.employee_id=s.employee_id)

When matched then
update set d.salary = d.salary + s.salary*0.01

Delete where (s.salary>8000)--from table 都不要......

When not matched then

Insert(d.employee_id,d.salary)

Values(s.employee_id,s.salary*0.01)

Where(s.salary<=8000);

SELECT * FROM bonuses ORDER BY employee_id;

EMPLOYEE_ID BONUS

----------- ----------

153 180

154 175

155 170

159 180

160 175

161 170

164 72

165 68

166 64

167 62

171 74

172 73

173 61

179 62
以下内容为转载

5、练习

2011-12-24 MERGE语句

作者：Jeff Kemp

难度：中

我创建了两张表并填入一些数据：

CREATE TABLE plch_empdept

( empid NUMBER NOT NULL

, deptid NUMBER NOT NULL

, CONSTRAINT plch_empdept_pk PRIMARY KEY (empid, deptid)

);

CREATE TABLE plch_newrecs

( empid NUMBER NOT NULL

, deptid NUMBER NOT NULL

, deleteme CHAR(1) NOT NULL

, CONSTRAINT plch_newrecs_pk PRIMARY KEY (empid, deptid)

);

INSERT INTO plch_empdept VALUES (10, 100);

INSERT INTO plch_empdept VALUES (10, 200);

INSERT INTO plch_newrecs VALUES (10, 200, 'Y');

INSERT INTO plch_newrecs VALUES (10, 300, 'N');

INSERT INTO plch_newrecs VALUES (10, 400, 'Y');

COMMIT;

然后我执行了一些SQL(即下列的每个答案)，目的是向 plch_empdept 表加入数据, 来源是 plch_newrecs 表中的deleteme等于'N' 的数据(假如数据在plch_empdept中已经存在则不修改), 如果 plch_newrecs的deleteme等于'Y'则从 plch_empdept删去相应数据。对应关系由组合键(empid, deptid)决定。