Changing SQL Update Statement to Working SalesLogix v7.5

Question: I have a SQL Update statement that worked in SalesLogix v6.1 but is not
working in v7.5.  I know it needs to be updated, but I can’t get it to
work:

UPDATE ADDRESS
SET UF_ACCOUNTMANAGERID = PC.ACCOUNTMANAGERID, UF_SECCODEID = PC.OWNERID

FROM UF_POSTALCODE PC, ADDRESS A, ACCOUNT AC, USERINFO UI
WHERE A.POSTALCODE = PC.POSTALCODE
AND AC.ACCOUNTID = A.ENTITYID
AND AC.ACCOUNTMANAGERID = UI.USERID
AND LEFT(A.ENTITYID,1) = ‘A’

AND (A.ISPRIMARY = ‘T’ OR A.ISPRIMARY IS NULL)
AND PC.POSTALCODE IS NOT NULL
AND (PC.ROUTING IS NULL OR PC.ROUTING = ”)
AND UI.DEPARTMENT = ‘SALES’
AND (UF_ACCOUNTMANAGERID<>PC.ACCOUNTMANAGERID OR

UF_ACCOUNTMANAGERID IS NULL OR UF_SECCODEID <> PC.OWNERID OR
UF_SECCODEID IS NULL)

Where am I going wrong?
 
Answer: Try using something similar to this:

UPDATE ADDRESS
SET
UF_ACCOUNTMANAGERID = (Select ACCOUNTMANAGERID From UF_POSTALCODE PC Where PC.PostalCode = Address.PostalCode)

UF_SECCODEID = (Select OWNERID From UF_POSTALCODE PC Where PC.PostalCode = Address.PostalCode)
Where
Exists (Select
1
FROM
Address AD
Inner Join UF_POSTALCODE PC On PC.PostalCode = A.PostalCode

Inner Join ACCOUNT AC On AC.AccountID = A.EntityID
Inner Join USERINFO UI On UI.UserID = AC.AccountManagerID
WHERE
AD.AddressID = Address.AddressID

AND LEFT(A.ENTITYID,1) = ‘A’
AND (A.ISPRIMARY = ‘T’ OR A.ISPRIMARY IS NULL)
AND PC.POSTALCODE IS NOT NULL
AND (PC.ROUTING IS NULL OR PC.ROUTING = ”)

AND UI.DEPARTMENT = ‘SALES’
AND (UF_ACCOUNTMANAGERID<>PC.

ACCOUNTMANAGERID OR
UF_ACCOUNTMANAGERID IS NULL OR UF_SECCODEID <> PC.OWNERID OR
UF_SECCODEID IS NULL)
)
 
 
 

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!