Update field using linked server

[fralo]

I need to update a database field in server ESO-CAD based on a matching value in server KRONOS. The problem is that the needed value itself on server KRONOS involves a join between two tables.

SELECT b.personnum,a.emailaddresstxt
FROM EMAILADDRESS a,PERSON b
WHERE a.PERSONNUM = b.PERSONNUM

With these returned values, I need to update field EMAIL on server ESO-CAD with value a.emailaddresstxt where there is a matching personnum with the one returned above.

Hope I'm making sense.

Thank you for your help.

 

[bborissov]

UPDATE TheTable
       SET eMail = RemoteTable.eMail
FROM [ESO-CAD].[DataBaseName].Schema.TableName TheTable
INNER JOIN (SELECT b.personnum,
                   a.emailaddresstxt
            FROM [KRONOS].[DataBaseName].Schema.EMAILADDRESS a
            INNER JOIN [KRONOS].[DataBaseName].Schema.PERSON b
                  ON a.PERSONNUM = b.PERSONNUM) RemoteTable
      ON TheTable.PersonNum = RemoteTable.PERSONNUM

NOT TESTED!


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

try this:

Update EsoCadTable
Set    EmailAddressColumn = AliasName.EmailAddresstxt
From   [ESO-CAD].DatabaseName.dbo.TableName As EsoCadTable
       Inner Join (
         SELECT b.personnum,a.emailaddresstxt
           FROM EMAILADDRESS a,PERSON b
          WHERE a.PERSONNUM = b.PERSONNUM
          ) As AliasName
          On EsoCadTable.PersonNum = AliasName.PersonNum

You'll need to modify some of the names (linked server name, table names, columns names, etc...).

If this works for you, and you would like me to explain it, just let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fralo]

One quick necessary addition to this query just came to mind. I need to only update certain records within the table. Adding a WHERE clause after the SET should do it, right?

Update EsoCadTable
Set EmailAddressColumn = AliasName.EmailAddresstxt
Where hide = '0'
From [ESO-CAD].DatabaseName.dbo.TableName As EsoCadTable
Inner Join (
SELECT b.personnum,a.emailaddresstxt
FROM EMAILADDRESS a,PERSON b
WHERE a.PERSONNUM = b.PERSONNUM
) As AliasName
On EsoCadTable.PersonNum = AliasName.PersonNum

 

[bborissov]

WHERE should be place AFTER the JOIN clause and IF HIDE field is common you should add the Alias of the table before it (depending whoch table you want to be filtered):

Update EsoCadTable
Set    EmailAddressColumn = AliasName.EmailAddresstxt
From   [ESO-CAD].DatabaseName.dbo.TableName As EsoCadTable
       Inner Join (
         SELECT b.personnum,a.emailaddresstxt
           FROM EMAILADDRESS a,PERSON b
          WHERE a.PERSONNUM = b.PERSONNUM
          ) As AliasName
          On EsoCadTable.PersonNum = AliasName.PersonNum 

Where ????????????.hide = '0'

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.