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

Question

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

Answer 1

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