2

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
Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
H Jansen
  • 319
  • 1
  • 8
  • 1
    What statement do you use to update the contacts? – Patrick Hofman Oct 26 '16 at 07:55
  • The following statement is used.: insert into contacts@freshdesk (name ,address ,phone ,email ) select deliveryaccountname , fulladdress , phone , email from FreshdeskTickets@inmemorystorage – H Jansen Oct 27 '16 at 08:01
  • @a_horse_with_no_name He uses the [Freshdesk API](https://freshdesk.com/api) with [Invantive SQL](http://www.invantive.com/products/invantive-sql) as SQL engine in front of that. The SQL engine translates the SQL statements to calls to the API. – Patrick Hofman Oct 27 '16 at 08:21
  • @a_horse_with_no_name It isn't a database platform indeed. It just mimics that for sources that aren't actually a DBMS (or those who are hidden behind a web service). – Patrick Hofman Oct 27 '16 at 08:24
  • And for Freshdesk, I don't have a clue which DBMS they are using. And for the question it isn't relevant actually. – Patrick Hofman Oct 27 '16 at 08:27

1 Answers1

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
Guido Leenders
  • 4,232
  • 1
  • 23
  • 43
Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
  • 1
    Note that last construct with minus only works when tuple of name, address, phone and email is correct combination. In this case, when address is different no row is removed from the first part of the select. You can consider the use of 'email not in ( select email from contacts@freshdesk)'. – Guido Leenders Oct 27 '16 at 19:56