Mule: How to Upsert Data in the Oracle Database

Most of the time, we get into a situation where we need to perform an upsert operation (update the record if else Insert exists). We do not have a separate database operation to achieve this upsert functionality. Below, you will see this upsert functionality achieved using the Merge function in the database.

GOAL: You want to perform Bulk Updates in a Mule 4 Application.

NOTE: As a requirement, you will need to use the DB Connector 1.5.1 version or above.

SQL Merge is an Update function, so you will need to use the Update Connector.

See the code below:

<db:bulk-update doc:name="Bulk update" doc:id="ab77f2ce-8185-40ee-9704-99d5e4c31a43" config-ref="Oracle">
<db:sql >MERGE INTO destination USING origin ON (Id = :Id)
WHEN MATCHED
THEN UPDATE SET Id = :Id,
Price =
:Price
WHEN NOT MATCHED THEN INSERT (Id, Price) VALUES (
:Id, :Price)</db:sql>
<db:bulk-input-parameters ><![CDATA[#[[{'Id': 2, 'Price': 200m= }]]]]></db:bulk-input-parameters>
</db:bulk-update>

Further Reading

MuleSoft, Salesforce UPSERT

External Data Operations on Salesforce Analytics Using Mulesoft Salesforce Analytics Connector Part 1