Salesforce Update with Join clause

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
pherschel
Posts: 6
Joined: Wed 31 Jan 2018 22:00

Salesforce Update with Join clause

Post by pherschel » Thu 01 Feb 2018 15:42

I'm trying to perform an update where I join two tables:

UPDATE Contact
inner join Account on Account.Id = Contact.AccountId
set Contact.OwnerID = '00538000004tIuhAAE'
WHERE (Account.Type <> 'Prospect') and contact.mailingstate in ('ND', 'SD') and contact.OwnerID <> '00538000004tIuhAAE'

I get an error: You have an error in your SQL syntax at line 2, column 1: keyword 'set' expected

I've also tried:

UPDATE Contact
set Contact.OwnerID = '00538000004tIuhAAE'
from Account inner join Contact on Account.Id = Contact.AccountId
WHERE (Account.Type <> 'Prospect') and contact.mailingstate in ('ND', 'SD') and contact.OwnerID <> '00538000004tIuhAAE'

this gives me the error: Unexpected symbol 'from'

Is this type of update possible?
Thanks
-Pete

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Salesforce Update with Join clause

Post by MaximG » Fri 02 Feb 2018 10:54

Try rewriting the query as follows :

Code: Select all

UPDATE Contact
   SET Contact.OwnerID = '00538000004tIuhAAE'
  FROM [<your Linked Servere Name>]...[Contact] As Contact
         INNER JOIN [<your Linked Servere Name>]...[Account] As Account ON (Account.Id = Contact.AccountId)
  WHERE (Account.Type <> 'Prospect') And Contact.mailingstate in ('ND', 'SD') And Contact.OwnerID <> '00538000004tIuhAAE'

Post Reply