Hi @russell-jerseypost 

In CData Arc, the UPSERT queries are usually a combination of UPDATE and INSERT queries, i.e. 
If an ID value (Primary Key) exists, UPDATE the record with the incoming data.
OR
If an ID value (Primary Key) DOES NOT exist, INSERT the record with the incoming data.

So normally, the application expects you to do UPSERT on an ID value or the Primary Key of the table, any field that is unique to the table that identified a row.

In the screenshot you shared, if the ‘id’ column is indeed the Primary Key of the ‘Shipment’ table, you can simply select UPSERT by id OR you can write your own query to find the UPSERT key.

Thanks Ankit

Yes I do have a column `id` that is a primary key, however the data coming in doesn’t know the key. `id` is not supplied.

I have two other columns which form a composite unique constraint..

At the moment, if I try to INSERT SQL will throw and error. But I’d like Arc to check first and do an UPSERT instead.

Can you give an example of how to write my own query to find the UPSERT key.please?

@Ankit Singh , sorry, do you have an example of the SQL query to determine the key to use?

@russell-jerseypost - it depends on how complex the UPSERT query needs to be. 

The drop-down under “UPSERT by” works for most cases and uses a single non-key column to perform the lookup. 

For example, it looks like you have a SHIPMENT_NUMBER column, which sounds like it would work. If UPSERT By is set to SHIPMENT_NUMBER, the query build automatically would be:

SELECT `id` FROM `Shipment` WHERE `SHIPMENT_NUMBER`='value_from_input_xml'

If this is a match, the returned id is used in an UPDATE on the table, otherwise the record is inserted. You should only need to use the “Perform this query...” option if you have to make the lookup query more complex. In that case, note that the goal of the query will be to determine the key column only, so something like this would work:

SELECT `id` FROM `Shipment` WHERE `SHIPMENT_NUMBER`=@SHIPMENT_NUMBER AND `KEY2`=@KEY2

The @columnanme syntax in the WHERE clause here will reference the value that’s provided in the corresponding element name in the input XML.