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
insert into contacts@freshdesk
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:
updatethe 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.
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