Oracle merge data operation (merge)

SQL Standard in 2003 In, a new data operation statement was added ：MERGE（ Merge ）, It can be done at the same time INSERT Statement and UPDATE sentence , even to the extent that DELETE Statement operation .

Standard merge statement

Its basic syntax is as follows ：

MERGE INTO target [AS t_alias]
USING source_table [AS s_alias]
ON (conditions)

WHEN MATCHED THEN
  UPDATE
  SET col1 = expr1,
      col2 = expr2,
      ...
      
WHEN NOT MATCHED THEN
  INSERT (col1, col2,...) 
  VALUES (expr1, expr2...);

among ,target_table Is the target table of the merge operation .

USING Clause specifies the data source , It can be a table or a query statement .

ON Clause specifies the conditions for data merging , We usually use the equality of primary key or unique key as the condition of merging .

For each record in the data source , If there are matching records in the target table , execute WHEN MATCHED THEN Update operation of branch ; If there is no matching record in the target table , execute WHEN NOT MATCHED THEN Insert operation of branch .

case analysis

Example tables used in the case

The following operation will use two tables , among employee The table stores the basic information of employees , Including name, 、 Date of entry 、 Department number 、 Salary and other fields . Here are some of the data in this table ：

 

emp_devp Table is employee Backup table for .

The initialization scripts for these two tables can be found at the bottom of the article .
 

Example 1

Use MERGE Statement will employee In the table dept_id by 4 Merge employee information into emp_devp In the table ：

MERGE INTO emp_devp t
USING (SELECT emp_id, emp_name, sex, dept_id, manager,
              hire_date, job_id, salary, bonus, email
       FROM employee
       WHERE dept_id = 4) s
ON (t.emp_id = s.emp_id)

WHEN MATCHED THEN
  UPDATE
  SET t.emp_name = s.emp_name, 
      t.sex = s.sex, t.dept_id = s.dept_id, t.manager = s.manager,
      t.hire_date = s.hire_date, t.job_id = s.job_id, 
      t.salary = s.salary, t.bonus = s.bonus, t.email = s.email
      
WHEN NOT MATCHED THEN
  INSERT (emp_id, emp_name, sex, dept_id, manager,
          hire_date, job_id, salary, bonus, email) 
  VALUES (s.emp_id, s.emp_name, s.sex, s.dept_id, s.manager,
          s.hire_date, s.job_id, s.salary, s.bonus, s.email);

The judgment conditions of the above merging operations are... In the data source and target tables emp_id Whether it is equal or not . If equal , Update the data in the target table ; otherwise , Insert data into target table .

The first time you run the above statement ,emp_devp There is no data in the table , Therefore, for each record in the data source WHEN NOT MATCHED THEN Branch , That is, insert data .

After the successful execution of the above statement , We can view the merged data ：

 

Example 2

MERGE Statement also supports DELETE Clause , It can be used to delete matching data in the target table , for example ：

MERGE INTO emp_devp t
USING (SELECT emp_id, emp_name, sex, dept_id, manager,
              hire_date, job_id, salary, bonus, email
       FROM employee
       WHERE dept_id = 4) s
ON (t.emp_id = s.emp_id)

WHEN MATCHED THEN
  UPDATE
  SET t.emp_name = s.emp_name, 
      t.sex = s.sex, t.dept_id = s.dept_id, t.manager = s.manager,
      t.hire_date = s.hire_date, t.job_id = s.job_id, 
      t.salary = s.salary, t.bonus = s.bonus, t.email = s.email
      
  DELETE WHERE t.emp_name = ' Zhao '
      
WHEN NOT MATCHED THEN
  INSERT (emp_id, emp_name, sex, dept_id, manager,
          hire_date, job_id, salary, bonus, email) 
  VALUES (s.emp_id, s.emp_name, s.sex, s.dept_id, s.manager,
          s.hire_date, s.job_id, s.salary, s.bonus, s.email);

We are WHEN MATCHED THEN Added a... To the branch DELETE Clause and a WHERE Conditions .

If you run the above statement ,emp_devp The name in the list is “ Zhao ” Your employee records will be deleted .

  By inquiring , Find out emp_name by “ Zhao ” The records of have been deleted .

Sample tables and scripts

-- Employee information form 
CREATE TABLE employee
    ( emp_id    NUMBER
    , emp_name  VARCHAR2(50) NOT NULL
    , sex       VARCHAR2(10) NOT NULL
    , dept_id   INTEGER NOT NULL
    , manager   INTEGER
    , hire_date DATE NOT NULL
    , job_id    INTEGER NOT NULL
    , salary    NUMERIC(8,2) NOT NULL
    , bonus     NUMERIC(8,2)
    , email     VARCHAR2(100) NOT NULL
  , comments  VARCHAR2(500)
  , create_by VARCHAR2(50) NOT NULL
  , create_ts TIMESTAMP NOT NULL
  , update_by VARCHAR2(50) 
  , update_ts TIMESTAMP
    ) ;
COMMENT ON TABLE employee IS ' Employee information form ';
COMMENT ON COLUMN employee.emp_id IS ' Employee number , Since the primary key ';
COMMENT ON COLUMN employee.emp_name IS ' Employee name ';
COMMENT ON COLUMN employee.sex IS ' Gender ';
COMMENT ON COLUMN employee.dept_id IS ' Department number ';
COMMENT ON COLUMN employee.manager IS ' Superior manager ';
COMMENT ON COLUMN employee.hire_date IS ' Date of entry ';
COMMENT ON COLUMN employee.job_id IS ' Job number ';
COMMENT ON COLUMN employee.salary IS ' a monthly salary ';
COMMENT ON COLUMN employee.bonus IS ' Year end bonus ';
COMMENT ON COLUMN employee.email IS ' email ';
COMMENT ON COLUMN employee.comments IS ' Notes ';
COMMENT ON COLUMN employee.create_by IS ' The creator ';
COMMENT ON COLUMN employee.create_ts IS ' Creation time ';
COMMENT ON COLUMN employee.update_by IS ' Reviser ';
COMMENT ON COLUMN employee.update_ts IS ' Modification time ';
 
 
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (1,' Liu bei ', ' male ', 1, NULL, DATE '2000-01-01', 1, 30000, 10000, '[email protected]', NULL, 'Admin', TIMESTAMP '2000-01-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (2,' Guan yu ', ' male ', 1, 1, DATE '2000-01-01', 2, 26000, 10000, '[email protected]', NULL, 'Admin', TIMESTAMP '2000-01-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (3,' Zhang Fei ', ' male ', 1, 1, DATE '2000-01-01', 2, 24000, 10000, '[email protected]', NULL, 'Admin', TIMESTAMP '2000-01-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (4,' Zhugeliang ', ' male ', 2, 1, DATE '2006-03-15', 3, 24000, 8000, '[email protected]', NULL, 'Admin', TIMESTAMP '2006-03-15 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (5,' Huang Zhong ', ' male ', 2, 4, DATE '2008-10-25', 4, 8000, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2008-10-25 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (6,' Wei Yan ', ' male ', 2, 4, DATE '2007-04-01', 4, 7500, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2007-04-01 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (7,' Sun shangxiang ', ' Woman ', 3, 1, DATE '2002-08-08', 5, 12000, 5000, '[email protected]', NULL, 'Admin', TIMESTAMP '2002-08-08 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (8,' Servant girl ', ' Woman ', 3, 7, DATE '2002-08-08', 6, 6000, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2002-08-08 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (9,' zhaoyun ', ' male ', 4, 1, DATE '2005-12-19', 7, 15000, 6000, '[email protected]', NULL, 'Admin', TIMESTAMP '2005-12-19 10:00:00', 'Admin', TIMESTAMP '2006-12-31 10:00:00');
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (10,' Liao Hua ', ' male ', 4, 9, DATE '2009-02-17', 8, 6500, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2009-02-17 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (11,' guan ping ', ' male ', 4, 9, DATE '2011-07-24', 8, 6800, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2011-07-24 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (12,' Zhao ', ' Woman ', 4, 9, DATE '2011-11-10', 8, 6600, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2011-11-10 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (13,' Guan Xing ', ' male ', 4, 9, DATE '2011-07-30', 8, 7000, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2011-07-30 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (14,' Zhang Bao ', ' male ', 4, 9, DATE '2012-05-31', 8, 6500, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2012-05-31 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (15,' Zhao Tong ', ' male ', 4, 9, DATE '2012-05-03', 8, 6000, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2012-05-03 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (16,' Zhou Cang ', ' male ', 4, 9, DATE '2010-02-20', 8, 8000, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2010-02-20 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (17,' Ma Dai ', ' male ', 4, 9, DATE '2014-09-16', 8, 5800, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2014-09-16 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (18,' Fazheng ', ' male ', 5, 2, DATE '2017-04-09', 9, 10000, 5000, '[email protected]', NULL, 'Admin', TIMESTAMP '2017-04-09 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (19,' Pang Tong ', ' male ', 5, 18, DATE '2017-06-06', 10, 4100, 2000, '[email protected]', NULL, 'Admin', TIMESTAMP '2017-06-06 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (20,' Jiang Wan ', ' male ', 5, 18, DATE '2018-01-28', 10, 4000, 1500, '[email protected]', NULL, 'Admin', TIMESTAMP '2018-01-28 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (21,' Huangquan ', ' male ', 5, 18, DATE '2018-03-14', 10, 4200, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2018-03-14 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (22,' Mi Zhu ', ' male ', 5, 18, DATE '2018-03-27', 10, 4300, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2018-03-27 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (23,' Deng Zhi ', ' male ', 5, 18, DATE '2018-11-11', 10, 4000, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2018-11-11 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (24,' Jane Yong ', ' male ', 5, 18, DATE '2019-05-11', 10, 4800, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2019-05-11 10:00:00', NULL, NULL);
INSERT INTO employee(EMP_ID,emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES (25,' Sun Qian ', ' male ', 5, 18, DATE '2018-10-09', 10, 4700, NULL, '[email protected]', NULL, 'Admin', TIMESTAMP '2018-10-09 10:00:00', NULL, NULL);




-- Employee information table backup table 
CREATE TABLE emp_devp
    ( emp_id    NUMBER
    , emp_name  VARCHAR2(50) NOT NULL
    , sex       VARCHAR2(10) NOT NULL
    , dept_id   INTEGER NOT NULL
    , manager   INTEGER
    , hire_date DATE NOT NULL
    , job_id    INTEGER NOT NULL
    , salary    NUMERIC(8,2) NOT NULL
    , bonus     NUMERIC(8,2)
    , email     VARCHAR2(100) NOT NULL
    ) ;
COMMENT ON TABLE emp_devp IS ' Employee information table backup table ';
COMMENT ON COLUMN emp_devp.emp_id IS ' Employee number , Since the primary key ';
COMMENT ON COLUMN emp_devp.emp_name IS ' Employee name ';
COMMENT ON COLUMN emp_devp.sex IS ' Gender ';
COMMENT ON COLUMN emp_devp.dept_id IS ' Department number ';
COMMENT ON COLUMN emp_devp.manager IS ' Superior manager ';
COMMENT ON COLUMN emp_devp.hire_date IS ' Date of entry ';
COMMENT ON COLUMN emp_devp.job_id IS ' Job number ';
COMMENT ON COLUMN emp_devp.salary IS ' a monthly salary ';
COMMENT ON COLUMN emp_devp.bonus IS ' Year end bonus ';
COMMENT ON COLUMN emp_devp.email IS ' email ';