In Oracle, you can use the "MERGE" statement to perform an UPSERT, which updates a row in a table if it already exists or inserts a new row if it does not. The basic syntax for the statement is as follows:
MERGE INTO target_table
USING source_table
ON (condition)
WHEN MATCHED THEN
UPDATE SET target_table.col1 = source_table.col1,
target_table.col2 = source_table.col2,
...
WHEN NOT MATCHED THEN
INSERT (col1, col2, ...)
VALUES (source_table.col1, source_table.col2, ...);
- target_table: the table that you want to update or insert into
- source_table: the table or query that you are using to update or insert from
- condition: the condition that determines whether a row in the target table matches a row in the source table
For example:
MERGE INTO employees e
USING (SELECT employee_id, first_name, last_name FROM temp_employees) temp
ON (e.employee_id = temp.employee_id)
WHEN MATCHED THEN
UPDATE SET e.first_name = temp.first_name, e.last_name = temp.last_name
WHEN NOT MATCHED THEN
INSERT (employee_id, first_name, last_name)
VALUES (temp.employee_id, temp.first_name, temp.last_name);
This will update the first_name and last_name of the employee's table if the employee_id already exists in the table, otherwise, it will insert a new row with the data from the temp_employees table.
){kind=link}
0 Komentar