What should I put in the “”Perform this query to select the UPSERT key” box?
Solved
How to use the UPSERT query?
Best answer by Ankit Singh
Hi
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.
This topic has been closed for replies.
+1Hi
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?
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.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
