How to use the UPSERT query?

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?

@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.