Oracle join instead of insert?

Question

I currently have an insert statement in a perl script using a module DBIthat collects router interface data. It works every time, but obviously with a Unique constraint error, since some elements exist when the script is re-run. I'm trying to do a merge instead, but I'm not sure how to do this without selecting data from another table, as in the examples I saw. To better understand, a perl script collects data by running it sshon devices and storing certain information in variables. for example, the name of the interface will be $interface. and etc.

Current insert statement

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

I started writing the merge funtion function, but I can’t figure out how this will work, since all the merge operators select from other tables and map the data and then update / insert? In the example below, the data from two different tables and the mappings are selected, however I am only looking at one table and want to combine the 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); 

Thus, this is a combination of data in bonuses, but juxtaposing them with employees. Even if such an expression worked, is it even possible to use such an expression in a perl script?

Answer 1

MERGE . , DUAL. UNION ALL DUAL .

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);