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)
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.
UF_ACCOUNTMANAGERID IS NULL OR UF_SECCODEID <> PC.OWNERID OR
UF_SECCODEID IS NULL)
)
