Validation failed error when inserting/updating Contact into Freshdesk using SQL

I want to update an existing Contact in Freshdesk via API using Invantive Control for Excel Cloud All. Invantive Control raises a validation error that email address and phone should be unique: Validation failed duplicate_value: email. It should be a unique value.

It's not clear whether the SQL of Invantive Control translates into a CREATE or PUT command. In case of PUT it should be possible to update an contact that already exist.

My SQL statement:

insert into contacts@freshdesk
( name
, address
, phone
, email
)
select deliveryaccountname
,      fulladdress
,      phone
,      email
from   FreshdeskTickets@inmemorystorage

An insert statement creates new rows in the contacts table, it doesn't modify existing ones. It seems you are trying to create contacts that already exist in your Freshdesk instance.

I would propose the following:

• Insert the tickets based on the email address of the user. This seems weird, but possible since the Freshdesk API actually checks if a contact exists based before it creates a ticket for that user;
• Then update the existing contacts, filtering out the contacts that don't need updating. You can do what with a minus. For ease of use I would create a new temporary table to store the updates in.

Like this:

create table contacts_to_update@inmemorystorage
as
select distinct deliveryaccountname
,      fulladdress
,      phone
,      email
from   FreshdeskTickets@inmemorystorage
minus
select name
,      address
,      phone
,      email
from   contacts@freshdesk

Then update (note that the from syntax isn't available yet in public releases):

update contacts@freshdesk cfd
set    cfd.name = cto.name
,      cfd.address = cto.address
,      cfd.phone = cto.phone
from   contacts_to_update@inmemorystorage cto
where  cto.email = cfd.email

The temporary solution would be to only insert those contacts who aren't there yet. The minus should work for the insert on contacts too, like this:

insert into contacts@freshdesk
( name
, address
, phone
, email
)
select deliveryaccountname
,      fulladdress
,      phone
,      email
from   FreshdeskTickets@inmemorystorage
minus
select name
,      address
,      phone
,      email
from   contacts@freshdesk