CODEWITHSUNDEEP
sqloracle-databaseperloracle11gdbi
user8956060
user8956060

Reputation:

Oracle merge instead of insert?

I currently have an insert statement in a perl script using the DBI module that collects router interface data. It works each time, but obviously with Unique constraint error because some items exist when the script is re-run. I am trying to do a merge instead, but I am unsure of how to do it by not selecting data from another table like the examples I have seen. To better understand, the perl script collects data by running ssh to the devices and stores certain information into variables. for instance interface name will be $interface. etc.

The current insert statement

$dbh->do("INSERT INTO table VALUES (?, ?, ?, ?, ?)", undef, $interface, $id, $device, $description, $user);

I started writing the merge funtion, but I cannot seem to grasp how this will work, as all the merge statements do select from other tables and matching data and then do update/insert? The below example selects data from from 2 different tables and matching, however I am only looking at one table and want to merge new data.

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.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
 VALUES (S.employee_id, S.salary*.01)
 WHERE (S.salary <= 8000);

So it is merging data into bonuses but matching it from employees. Even if such a statement would have worked, is it even possible using such a statement in a perl script?

Upvotes: 5

Views: 4888

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

In your case, a MERGE statement like this should work. Note that I am selecting a single record from DUAL table. you may use multiple UNION ALLs from DUAL for multiple rows.

MERGE INTO yourtable target
     USING (SELECT 1 AS id, 'value_1' AS column_1, 'value_2' AS column_2
              FROM DUAL) source
        ON (source.id = target.id)
WHEN MATCHED
THEN
   UPDATE SET
      target.column_1 = source.column_1, target.column_2 = source.column_2
WHEN NOT MATCHED
THEN
   INSERT     (column_1, column_2)
       VALUES (source.column_1, source.column_2);

Upvotes: 5

Related Questions