Update on linked server with nested subquery

I want to update on a linked server the result of a query as well from a linked server.

The first sql开发者_如何学Go snippet gives me the value to be updated:

    SELECT  mmdb_vessel.IMONo, mmdb_vessel.DeathDate   
From OPENQUERY(MMDB, 'SELECT  FunctionalLocation, IMONo, VesselStatus, CONVERT(VARCHAR(10), DeathDate, 102) AS DeathDate
            FROM  VESSEL 
            WHERE VESSEL.VesselStatusID <> 42 AND VESSEL.DeathDate is not null') as mmdb_vessel
, eb_all_v
WHERE 
eb_all_v.IMO_No = mmdb_vessel.IMONo 
AND eb_all_v.status = 'in service'

the second is actually what I'm not able to implement, it should show what I want to achieve:

UPDATE EPI2..EPI.PLANT
SET KIND_OF_LIQUIDATION_NO = 1
, LIQUIDATION_DATE = [result from snippet above].DeathDate 
Where EPI2..EPI.PLANT.IMONo = [result from snippet above].IMONo 

I'm not so sure if my explanation is sufficient, please feel free to ask for additional information!

Thanks, already in advance, Werner

---

I would recommend to select the data from the remote server first and store the required data e.g. in a temptable, because LinkedServer and updates can have some sideeffects (e.g. performing a tablescan on the remote table, altough you would not expect it if an updaet is involved, etc) - but this depends on your exact usage/scenario.

1. Select data you need to update

SELECT * INTO #tmpTable FROM LINKEDSERVER.EPI.dbo.PLANT WHERE ....

1. Perform the update on local server

UPDATE EPI2..EPI.PLANT SET KIND_OF_LIQUIDATION_NO = 1, LIQUIDATION_DATE = t.DeathDate FROM #tmpTable t INNER JOIN EPI2..EPI.PLANT p on t.IMONo = p.IMONo