plsql - Oracle中使用merge语句删除数据

Question

如何使用 oracle 使用合并仅删除数据...
我正在使用以下代码:

Merge 
into 
target_table
using 
source_tablle 
on (...)
when matched 
then delete

但我在最后一行收到错误“缺少关键字”

Answer 1

您的 MERGE最后缺少UPDATE条款。
让我们看一个样本 MERGE

CREATE TABLE employee (
employee_id NUMBER(5),
first_name  VARCHAR2(20),
last_name   VARCHAR2(20),
dept_no     NUMBER(2),
salary      NUMBER(10));

INSERT INTO employee VALUES (1, 'Dan', 'Morgan', 10, 100000);
INSERT INTO employee VALUES (2, 'Helen', 'Lofstrom', 20, 100000);
INSERT INTO employee VALUES (3, 'Akiko', 'Toyota', 20, 50000);
INSERT INTO employee VALUES (4, 'Jackie', 'Stough', 20, 40000);
INSERT INTO employee VALUES (5, 'Richard', 'Foote', 20, 70000);
INSERT INTO employee VALUES (6, 'Joe', 'Johnson', 20, 30000);
INSERT INTO employee VALUES (7, 'Clark', 'Urling', 20, 90000);

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

INSERT INTO bonuses (employee_id) VALUES (1);
INSERT INTO bonuses (employee_id) VALUES (2);
INSERT INTO bonuses (employee_id) VALUES (4);
INSERT INTO bonuses (employee_id) VALUES (6);
INSERT INTO bonuses (employee_id) VALUES (7);
COMMIT;

现在我们有了一个示例数据结构，让我们进行一些合并:

MERGE INTO bonuses b
USING (
  SELECT employee_id, salary, dept_no
  FROM employee
  WHERE dept_no =20) e
ON (b.employee_id = e.employee_id)
WHEN MATCHED THEN
  UPDATE SET b.bonus = e.salary * 0.1
  DELETE WHERE (e.salary < 40000)
;

所以这个命令 MERGE使用 merge_update_clause 的语法: MERGE INTO (表/ View ) USING (表/ View ) ON (健康)状况) WHEN MATCHED THEN UPDATE SET (列..表达式) DELETE WHERE (健康)状况)

我想我的意思是你想念你的 UPDATE SET条款以及 DELETE状况。我建议跟进 MERGE句法。
**编辑:**SQLFiddle 回来了 so here you go .