How to use merge command in oracle?

by haylee.mertz , in category: MySQL , a month ago

How to use merge command in oracle?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

by mallory_cormier , a month ago

@haylee.mertz 

The MERGE command in Oracle is used to update or insert data into a target table based on a specified condition. Here is the syntax and an example of how to use the MERGE command:


Syntax:

1
2
3
4
5
6
7
8
MERGE INTO target_table
USING source_table
ON (condition)
WHEN MATCHED THEN
  UPDATE SET column1 = value1, column2 = value2
WHEN NOT MATCHED THEN
  INSERT (column1, column2)
  VALUES (value1, value2);


Example: Suppose we have two tables, employees and employee_updates. We want to update the employees table with any changes in the employee_updates table based on the employee_id. If the employee_id already exists in the employees table, we will update the salary column with the new salary from the employee_updates table. If the employee_id does not exist in the employees table, we will insert a new row with the employee details.

1
2
3
4
5
6
7
8
MERGE INTO employees e
USING employee_updates eu
ON (e.employee_id = eu.employee_id)
WHEN MATCHED THEN
  UPDATE SET e.salary = eu.salary
WHEN NOT MATCHED THEN
  INSERT (employee_id, name, salary, department)
  VALUES (eu.employee_id, eu.name, eu.salary, eu.department);


This MERGE command will update or insert records in the employees table based on the information in the employee_updates table.