# How to use the UPSERT query? **URL:** \ **Category:** CData Arc\ **Tags:** sql, question, upsert\ **Created:** [July 11, 2023, 4:10pm UTC](https://community.cdata.com/t/how-to-use-the-upsert-query/242 "2023-07-11T16:10:45Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![russell-jerseypost](https://avatars.discourse-cdn.com/v4/letter/r/439d5e/32.png) [@russell-jerseypost](https://community.cdata.com/u/russell-jerseypost)\ **Post date:** [July 11, 2023, 4:10pm UTC](https://community.cdata.com/t/how-to-use-the-upsert-query/242/1 "2023-07-11T16:10:45Z")
![eaf794a9-f279-4ed9-89f2-3d0c051ee132.png](https://us1.discourse-cdn.com/cdata/original/1X/cf1c7593965dc207722f498f7ad0fffc3f85b078.png) What should I put in the “”Perform this query to select the UPSERT key” box? ---
**Author:** ![Ankit\_Singh](https://sea3.discourse-cdn.com/cdata/user_avatar/community.cdata.com/ankit_singh/32/1614_2.png) [@Ankit\_Singh](https://community.cdata.com/u/Ankit_Singh)\ **Post date:** [July 12, 2023, 3:50pm UTC](https://community.cdata.com/t/how-to-use-the-upsert-query/242/2 "2023-07-12T15:50:12Z")
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. ---
**Author:** ![russell-jerseypost](https://avatars.discourse-cdn.com/v4/letter/r/439d5e/32.png) [@russell-jerseypost](https://community.cdata.com/u/russell-jerseypost)\ **Post date:** [July 14, 2023, 1:26pm UTC](https://community.cdata.com/t/how-to-use-the-upsert-query/242/3 "2023-07-14T13:26:46Z")
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? ---
**Author:** ![russell-jerseypost](https://avatars.discourse-cdn.com/v4/letter/r/439d5e/32.png) [@russell-jerseypost](https://community.cdata.com/u/russell-jerseypost)\ **Post date:** [September 11, 2023, 10:28am UTC](https://community.cdata.com/t/how-to-use-the-upsert-query/242/4 "2023-09-11T10:28:58Z")
@Ankit_Singh  , sorry, do you have an example of the SQL query to determine the key to use? ---
**Author:** ![James\_B](https://sea3.discourse-cdn.com/cdata/user_avatar/community.cdata.com/james_b/32/1688_2.png) [@James\_B](https://community.cdata.com/u/James_B)\ **Post date:** [September 11, 2023, 4:40pm UTC](https://community.cdata.com/t/how-to-use-the-upsert-query/242/5 "2023-09-11T16:40:51Z")
@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: ```sql 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: ```sql 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.