Salesforce

UPSERT Condition Screen (Magic xpi 4.13)

Printable View
« Go Back

Information

 
Created BySalesforce Service User
Approval Process StatusPublished
Objective
Description

UPSERT Condition Screen (Magic xpi 4.13)

Use the UPSERT Condition screen to enter an UPSERT/MERGE condition that will be sent to the database to be used in the UPSERT operation. The UPSERT/MERGE statement enables you to update the record if it exists in the Database otherwise insert the same.

For the list of databases supported for the UPSERT operation refer Database Resource section. If the database doesn’t support UPSERT operation, appropriate error will be displayed on the Data Mapper tree.

The UPSERT Condition screen contains the following fields:

Name

Description

Available Columns

A list of all the columns in the tables that you selected. Double-click on a column to add it to the UPSERT Condition text field.

Variables

A list of variables that you can use. Double-click on a variable to add it to the UPSERT Condition text field.

UPSERT Condition

Displays the UPSERT Condition according to the items that you selected in the Available Columns and Variables fields (above). This text should not exceed 10000 characters.

The Wizard will generate the actual UPSERT/MERGE statement on the Summary screen as per the Database type selected in the Resource.

UPSERT statements are derived from file 'Studio\Templates\Database\SQLTemplates.xml'.

To Use the UPSERT Condition Screen:

  1. Create an UPSERT statement by double-clicking the required Available Columns and Variables. The selected items will be entered in the UPSERT Condition text field in the order you select them.

  2. When you have entered the information, click Next. This will take you to the Summary screen.

  3. Join Statements screen is not applicable. It will be skipped while working with the UPSERT operation.

When working with UPSERT conditions, you should be aware of the following:

  • You need to add single quotes around selected Alpha variables for UPSERT operation.

  • To specify a dynamic variable with a value that can be specified in the mapping, show that the database_field=<!?variablename?!>. For example, order: OrderID=<!?MyOrderNumber?!>. This will cause the MyOrderNumber variable to appear in the Data Mapper Destination as a part of the UPSERT composite element.

  • It is not recommended to update the columns used in the UPSERT condition. You can manually delete those columns from the SQL statement.

  • MSSQL allows the user to update the fields which are included in the UPSERT condition in the UPDATE part of the Database schema whereas Oracle database doesn’t support it and gives an error at runtime.

UPSERT Condition Behavior for Different Databases

Databases have varying capabilities and will have different implementations for the UPSERT operation.

Some databases might use a Merge statement using relations, to identify if an existing row is being used, while other databases might handle the duplication events during insert and move to update operation instead.

Due to this varying nature of databases, Magic xpi will handle the UPSERT operations for each database differently. The wizard will, therefore, behave differently for each database as described below:

PostgreSQL

PostgreSQL database has the following limitations:

· Boolean conditions and Dynamic values or columns are not supported.

· Only physical columns are allowed in the UPSERT conditions.

For example,

o public.order.orderid

o public.order.orderid,public.order.ordername

MySQL

The UPSERT condition screen is not available for the MySQL database. MySQL database handles the duplication of records internally based on the Unique/Primary key defined on the table.

DB2&DB2/400

Like other databases, for DB2 &DB2/400 databases UPSERT and MERGE commands can be constructed using uses various combinations. The wizard allows the users to configure only basic UPSERT operations.

Advanced operations can be configured by setting the value of Dynamic SQL property to Yes or by making changes to the statement in the Wizard Summary screen.

  • Logical names can be used as a part of the Using section in the UPSERT Query.

  • The UPSERT statement generated with USING statement and ON condition should evaluate to a single row in case the Source and Target tables are the same. In case it returns more than one record, a "Duplicate key value specified" error will be thrown.

As shown below, the UPSERT conditions for these databases can be defined in two different ways and both are supported in Magic xpi.

(tgt.CLIENT_NAME,tgt.CLEINT_VAT) = (xxxx,'demo')

OR

tgt.CLIENT_NAME='xxxx' and tgt.CLEINT_VAT='demo'

The Merge statements for both these combinations will look as below:

MERGE INTO SQLMERGE.MERGE_CLIENT AS tgt USING (SELECT CLIENT_ID,CLIENT_NAME,CLEINT_VAT,CLIENT_BILL_ADDRESS,CLIENT_SHIP_ADDRESS FROM SQLMERGE.MERGE_CLIENT)

AS tgt ON ((tgt.CLIENT_NAME,tgt.CLEINT_VAT) = ('xxxx','demo'))

WHEN MATCHED THEN UPDATE SET CLIENT_BILL_ADDRESS=1991,CLIENT_SHIP_ADDRESS=(tgt.CLIENT_SHIP_ADDRESS+10)

WHEN NOT MATCHED THEN INSERT (CLIENT_ID,CLIENT_NAME,CLEINT_VAT,CLIENT_BILL_ADDRESS,CLIENT_SHIP_ADDRESS) VALUES ((tgt.CLIENT_ID+30),'xxxx','demo',66,777)

OR

MERGE INTO SQLMERGE.MERGE_CLIENT AS tgt USING (SELECT CLIENT_ID,CLIENT_NAME,CLEINT_VAT,CLIENT_BILL_ADDRESS,CLIENT_SHIP_ADDRESS FROM SQLMERGE.MERGE_CLIENT)

AS tgt ON ((tgt.CLIENT_NAME= 'xxxx' AND tgt.CLEINT_VAT='demo'))

WHEN MATCHED THEN UPDATE SET CLIENT_BILL_ADDRESS=1991,CLIENT_SHIP_ADDRESS=(tgt.CLIENT_SHIP_ADDRESS+10)

WHEN NOT MATCHED THEN INSERT (CLIENT_ID,CLIENT_NAME,CLEINT_VAT,CLIENT_BILL_ADDRESS,CLIENT_SHIP_ADDRESS) VALUES ((tgt.CLIENT_ID+30),'xxxx','demo',66,777)

(Since version: 4.9)

Reference
Attachment 
Attachment